Merge excel files in uipath

Help
1

I have three excel file and want to merge it to one.
How to do it.

2

Hello @Tanvi_Sharma.
For example you can use Read Range Activity for all three files to keep data in three DataTable variables and the use Write Range for all three to save data in one excel file.

2 Likes
3

Buddy @Tanvi_Sharma

  1. Use excel application scope to read all those three excel files and use read range to get the datatable…now to merge the datatable we have an option called merge datatable where provide theiput as out_dt_1 as source and out_dt_2 as destinaton
    again use merge datatable and with source out_dt_2 and out_dt_3 as destination
  2. Now use write range to write that datatable to an excel file you want

But before merging them we must be clear that all the datatable columns are in same order, datatype and same naming conventions

Hope this would help you

Cheers

4

Is that working buddy @Tanvi_Sharma

Cheers

5

Hi there…

Well, if you are more inclined towards a custom activity with many options, you can use the following code in the link provided.

https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Excel-Merge-Merge-Excel-Files-into-One-with-C-VB.NET.html

So what it does is simply merge all data into a single excel file.

If you need formatting options and different sheets to be created you might have to add more features.

Alternatively, lets say you have three excel files, run an external macro which will collate everything into a single file if custom activity is not your cup of tea.

Or if you dont want anything external - use UiPath Datatable to merge all data and write to a newly created excel file.

Cheers…!

1 Like
6

Hi @Tanvi_Sharma

You can watch this below video about merging excel sheets using merge DT

Thanks,
Ula

7

HI @Tanvi_Sharma

I have a sample xaml file which I had created few days back to assist one of my friends here in the forum. I think it should help you as well. This basically takes data from two excel files and merge’s them together and writes back to another excel.

I guess this is what you are looking for. Depending on your scenario, you can try changing it to get it optimized as you require. For example, if you have all the excel files that you need to read in one particular folder, you can get the files from the folder and loop through them.

Merge two excel files
mergeTwoExcelSheets.xaml (6.5 KB)

In case you need to get multiple files from one single folder and merge them all, refer this.
merge datatable.xaml (9.8 KB)

Hope it helps

7 Likes
9

Very efficient for merging multiple files compared to the way I was doing it, that also had some issues with preserving date and number formats, so switched to this rather than debug. To preserve headers, I copied a template file with just headers, read it in an Excel Application Scope using a dummy Data Table, then used Write Range with the data in the resultant Data Table (a very minor enhancement in reality) . Thanks @Lahiru.Fernando ! So happy to start the weekend this way :grinning:

2 Likes
10

Hi All!

I have used 3 Read Range to read 3 excel files and combine them into one using Write Range but encountered the error below.

image

Note that my files are under .xls extension. I am also using the read range activity under Workbook tab.

11

Hi @Tanvi_Sharma,

Here is the activity to merge the excel files.

Regards
Balamurugan.S