Want to append data from multiple excel files to one excel file

Help
,
1

I have to collate multiple excel data into one excel file. Can I use append range in For Each File? If yes then please advise how?

1 Like
2

Hey @MJKHAN,

Good to see you here.

Yes, you are absolutely right. Append range can be used in for-each to consolidate all data into one sheet.

Please make sure all the excel sheets follow same schema to avoid any exception.

Thanks :slight_smile:

3

Are the names of the columns are same in all excel files?
If yes - then you can excel file or else
Check this video if columns name are different: Understanding Lookup Data Table With Use Case - YouTube @MJKHAN

4

Columns name are same, but files names are different. How can I use append range in For each File? I do not have coding knowledge to write code. Could you please help me step by step.

5

Hi @MJKHAN,

  1. Say you have your input excel or csv files in the a folder called “Data” in the UiPath project folder. You could then use Directory.GetFiles("Data") to get all file and their names. It will be saved as a array of string
  2. Define of initilize your OutputDatatable using the Build Datatable activity
  3. Use a for each loop to iterate through the array, where item.ToString is your filename
  4. Use item.ToString in a Read Range Activity and store the content as Input Datatable
  5. For each row in Input Datatable Use Add Data Row (here you want to add the row values as array and choose to add rows to OutputDatatable
  6. Write the OutputDatatable to either csv or xlsx (remember to mark AddHeaders in the properties)

This will give you the required output. Try not to save the Ouput.xslx in the same Data folder though, if you do that then you will read the Output.xslx as an input file when you run the workflow nextime.

Here is the suggested approach (I dont have excel installed so I use the system activity Read Range / Write Range without Excel scope): CollateMultipleExcelFiles.zip (21.5 KB)

Hope this helps!

2 Likes
6

Hi sorry to revive and old question,

how to do this if i want to input different column (as opposed to different number of row as outlined in your data?) because my excel have different number of column (because it is monthly) and different number of row (not always, but sometimes there’s new item coming in so need to add the row as well) thank you

1 Like
7

I think that shouldn’t be a prob unless and until the source and destination sheets have same schema.

#nK