How to Append multiple .xlsx in one file xlsx

Help Studio
, ,
1

hello, premise
that I have no programming experience, I have to append an variable number of .xlsx files to a folder.

The number of files varies every day, but the structure is the same for all files. (number of columns and column names)

I would like to obtain a single xlsx file that satisfies all the above, concatenated.

I can’t use excel Application Scope.
Can anyone help me?
If it’s easier, in the same folder I have the same files, in csv.
But in the end I have to get an .xlsx.

can help me with this?

2

Hey!

Yeah so instead of using an Excel Scope, you can use the Read Range activity under Workbook (this doesn’t require Excel to be installed).

My steps would be:

  • build a master or main datatable
  • create a for each loop that loops through each xlsx file in the “input folder”
  • read each file into a datatable and append it to the master datatable
  • write the master datatable to file once all of the files have been read.

Does this make sense?

3

Thanks for the reply Jacqui_M,
I am sure that for most of the people present in this Forum, your indications are complete and exhaustive.
Unfortunately, I am not very familiar with and programming knowledge.
I dived into this environment, and I try to take the first steps.

I know how to create a datatable … this is very easy.
I know how to write the final DT, on an .xlsx file.

But I don’t know, concretely, how to write this part.

  • create a for each loop that loops through each xlsx file in the “input folder”
  • read each file into a datatable and append it to the master datatable

I realize that it is the “heart” of automation … sorry.

4

Hey @AaronMark

It’s a great place to start.

I would suggest looking at the training on the Academy. It really is useful to grasp a general understanding on some of the topics.

1 Like
5

HI Jaqui…
can you give me additional help.
I did this:
Sequence.xaml (13,9 KB)

“it works” … partially.
I don’t know how to add the header line in the final file.
I did some tests, but I think something is missing.

Can you give me some indication?

Thanks…
A

6

Hi @AaronMark,

Read range and write range properties add header and try

image
image1365×738 64 KB

Regards,
Omkar P

7

image
image821×174 6.86 KB

I would like to set the specific name of each column.
I think I used the DT and ADD ROW Build incorrectly.

8

Yes it is taking default value

9

Do you know how to set a different value?
I have the names of the columns.

10

if yo don’t mind provide your code are your input file.I will help you

11

Conversation-messages-Mon Jun 01 2020-Thu Jun 04 2020-1of4.xlsx (17,1 KB)
Conversation-messages-Mon Jun 01 2020-Thu Jun 04 2020-2of4.xlsx (20,6 KB)

Sequence.xaml (13,9 KB)

12

ok thank you

13

I Must KEEP only this columns.
eventKey , eventId, conversationId, time, agentGroupName

after append… :expressionless:

my script writes this line, but inserts it as the last one.

14

Do u need any empty line in-between each datatable while appending

15

Hi @AaronMark,

Please find the solution.If you have any issues.Let me know.

@I made some changes compare your file and this file you will have idea.

Sequence1.xaml (13.7 KB)

New Microsoft Excel Worksheet.xlsx (8.1 KB)

Regards,
Omkar P

16

Hi Reddy…
I saw this when open your file.

image
image721×53 4.02 KB

do I have to upload any new packages?

17

uhm…
How do I set it up?
Do I start writing from A2 instead of A1?

18

You are studio version?

19

Yes.
2019.4.5

image

20

My pc
image

image