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?
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.
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.
- 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
- Define of initilize your
- Use a for each loop to iterate through the array, where item.ToString is your filename
- Use item.ToString in a Read Range Activity and store the content as
- For each row in
Add Data Row(here you want to add the row values as array and choose to add rows to
- Write the
OutputDatatableto 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!