Merge 3 datatables with the same column name into one with different column names

Hey guys!
Asking again for your help. I have 3 datatables(Sheet1, Sheet2, Sheet3) that have the same Column name (Column-0) and i need to merge all of them into one datatable(Sheet4) but the Columns should be Column-0, Column1, Column 2.
Check the attached screenshot:


image
image

Tried merge datatable but it looks like this:


ANy other ideas?
Much appreciated!!!

1 Like

Hi

Hope the below steps would help you resolve this

  1. Use a excel application scope and pass input with filepath of excel

  2. Inside the scope use a read range activity and read sheet1 and get output as dt1

Same for sheet2 use another read range activity and get output as dt2

Similarly sheet3 - dt3

  1. Now use a ADD DATACOLUMN activity and pass datatable as dt1 and column property as dt2.Columns(0)

  2. Again use add datacolumn activity and pass datatable as dt1 and column property as dt3.Columns(0)

Now dt1 is the final datatable with all three columns merged together as one datatable

Cheers @RaduNRV

Hi @RaduNRV

Please try,

  1. Read range activities and store all the three datatables into dt1, dt2 and dt3 variables.

Use 2 assign activities

Assign 1
Dt2.columns(0).columnname = “column-1”

Assign 2
Dt3.columns(0).columnname = “column-2”

  1. Use three write range activities and write the datatable into a new sheet.

Write range 1 - cell value should be a1 and datatable is dt1

Write range 2 - cell value should be b1 and datatable is dt2

Write range 1 - cell value should be c1 and datatable is dt3

And finally read the new sheet and store that into a datatable.

Thanks

we can do it with a side by side merge

Hey @Palaniyappan

Thanks for your reply. I tried it but i’m getting error:

image

1 Like

Fine I got it

The reason is all the columns in sheet1, sheet2 and sheet3 are same

Fine next to this steps have this one step added

Now we have dt1, dt2, dt3

  1. Use a assign activity like this

dt2.Columns(0).ColumnName = “Column-2”

And again another assign activity like this

dt3.Columns(0).ColumnName = “Column-3”

Now follow these steps it should work fine

Cheers @RaduNRV

Did what you said but it looks like this:

image

Can you test it out on your side?
Maybe provide a xaml. I would appreciated very much!

tried it but it gives me this:

image

i forgot something in the code. Works fine. Many thanks!!!

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.