Merging of excel sheets

Help
, ,
1

Hi robochamps,

Can someone give me a better way to merge Excel files which contain similar sheets with similar names and columns but with different data File2.xlsx (10.3 KB) File2.xlsx (10.3 KB) File2.xlsx (10.3 KB)

I have attached sample files, i have to create a fourth file with all the data from the above files, sheets.

Thankyou!! in advance

1 Like
2

Hi
did we try with MERGE DATATABLE ACTIVITY
Kindly have a view on this for more insights

Cheers @Shikhar_Tandon

2 Likes
3

You have the activity of merge datatable to do so.

2 Likes
4

In a While loop (condition could be if there are files present in a source folder to process, moving processed files to another folder), Read each file into a datatable, then add each row to a Final Output datatable. Move the processed file to another folder.

Outside the loop then write the final datatable to Excel.

5

Thanks for the reply @Palaniyappan,
We tried it with MERGE DATATABLE activity but i wanted some other way rather than that
Please guide if you know thanks !!

6

hey @ronanpeter, thanks!!, Can you explain a little more.

7

May i know why so
because that is the most easy and efficient way to handle this buddy
@Shikhar_Tandon

8

Sure, slight variation on what I explained and it uses the MergeDatatable activity suggested elsewhere also. It looks to a specified folder of excel files, and merges them as longs as:

  1. The files have the same column names as defined in the ‘Output Table’ excel file.
  2. The sheet names are the same - in the example I merge only the “Key” sheets.

It produces a CSV output with a table of all merged data but can easily be adapted to write the data into the Output Table file.

MergeExcelFiles.zip (26.4 KB)

9

Hi @Shikhar_Tandon,

Here is the activity to merge the 2 excel files …

Regards
Balamurugan.S