Copy folder with Excel files with columns together into a new Excel file

[Matthias_Kuntz]

Hello everyone,

I am still relatively new to UiPath. I have already managed to do a few things with Studio.

I have now created a folder with several Excel files with content.
I now want to copy the data from these several Excel files all together into a new Excel file. I wanted to use this with a foreach loop. But I’m still not quite sure how to do this. Is it important to first integrate the data from the various Excel files into a DataTable and then copy it into a single Excel file? Does anyone happen to have a good tutorial? I only ever find ones where only a single Excel file is copied.

[lrtetala (Lakshman Reddy)]

Hi @Matthias_Kuntz

Welcome to Community!!

Can you try the below code to write multiple excels to single excel in different sheets

Input:

Output:

Sequence2.xaml (17.1 KB)

or

If you want to write multiple excel into single excel in single sheet then try below code

Merging Excels.xaml (18.9 KB)

Cheers!!

[mkankatala (Mahesh Kankatala)]

Hi @Matthias_Kuntz

If the sheet name and columns are same in all the excel files, we can do it by using append range workbook activity by looping through every excel file using for each file in folder activity.

→ Take an assign activity and create a count variable with Int32 datatype and initialize with value 1.
→ Take for each file in folder activity to loop through each excel file.
→ In the Filter by option in for each file in folder activity give the extension of the file if it is xlsx or xls. If it is xls give like this “.xls" or If it is xlsx files give like this ".xlsx”
→ Inside for each use the read range workbook activity to read the each excel file and store the output in a datatable datatype variable called Input_dt.
→ After read range workbook activity insert the If activity to check the condition

- Condition -> Count = 1

→ In then block give the write range workbook activity and give the destination path of excel file and sheet name, check the Add headers option in properties and pass the Input_dt variable.
→ In else block give the append range workbook activity and give the same destination path of excel file and sheet name which is given in write range workbook activity and pass the Input_dt variable.
→ Outside of If condition take an Clear datatable activity and pass the Input_dt variable which clears the data in the variable for every iteration.

Check the below workflow for better understanding,

Hope it helps!!

[rikulsilva (Henrique Lima da Silva)]

Hi @Matthias_Kuntz

Welcome to Community

You also can use Merge Data Table Activity depending the Excel struture. It will give to you some options like

Ignore - Only merge columns that matchs the destination DT
Error - Throw a error if some column doesn’t match destination DT
Add - Add Columns all columns including the ones that don’t match the destination DT

So if Excel files have different columns, Add option may help you

Cheers

[Matthias_Kuntz]

Thank you very much for your active support. I was able to realise it the way, I wanted with your solutions.