How to merge datatables obtained from a loop which gets datatable corresponding to each worksheet (having same headers) in an excel

Learn Academy Feedback
,
1

Hi

I am new to UiPath and currently working on a problem, to combine all Sheets (having same headers) into one Sheet.

For this I have read all Sheets using Get Workbook Sheets, and then have looped them to read the datatable from each sheet, using ForeEach. Check Screenshots below.

Now I want to combine all these datatables into one datatable, how can I do it.

image

image

Thanks in advance.

Regards
Ankit

2

Please check https://activities.uipath.com/docs/merge-data-table

3

Hi @ankswe

If the column headers are somewhat similar or each table has some matching columns then you can use merge datatable activity to merge the tables.If the tables has even one matching column then you can set MissingSchemaAction Property to Add in the properties pane.

4

Hi Amarasto

All sheets have same columns. But what it do is instead of appending the data from second sheet onwards, it is actually overwritting it.

So, if say first sheet has 2 rows, it is overwritten by data of second sheet which has 3 rows, and this continues.

Regards
Ankit

5

Hey @ankswe,

Have You tried Append Datatable Activity?

6

Hello Ankit

Check what datatable you are using in source and destination column

You would need a merge datatble activity for each time you wish to merge a datatable

Eg:
DT1 (from sheet 1) as Source and DT_Master as destination
DT2(from sheet2) as sSOurce and DT_Master as destination

and so on

Make sure Missing schema action in properties of Merge data table is set to add

8

Hello neonova,

I am trying exactly the same you mentioned but still I am facing the same issue which Ankit was facing. Merge datatable activity is actually overriding the destination data table after every iteration instead of merging the source data table .
Thanks!!!

9

Hello Team,
Im trying to merge data,
Please have a look at workflow,

image
image515×746 15.3 KB
,
Throwing error - image

10

Perhaps you have already resolved this. I found your question because I faced a similar problem. I believe that the solution lies in variable scope.

My merged data table was declared in the body of my loop. Since you have to allocate a data table using new this means that the merged data table is being wiped each iteration of the loop. It isn’t that the merge table command is overriding the table, rather the merged table begins anew each cycle. To fix this problem, I declared the data table above and outside of the loop (Just change the scope to the parent activity of the loop).

I hope this helps anyone else who runs into this problem. If you are tinkering and start declaring variables within an activity you may accidentally limit or expand their scope.

11

Hi man , I am facing the same issue and I have declared both the datatables to the parent scope.