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

[RaduNRV (Radu Valentin Neacsu)]

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:

Tried merge datatable but it looks like this:

ANy other ideas?
Much appreciated!!!

[Palaniyappan (Forum Leader)]

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

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

4. 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

[prasath_S]

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”

2. 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

[ppr (Peter)]

we can do it with a side by side merge

How to add three data tables into one data table

@Kavya_Mamidishetti2 Create an empty datatble with the three cols with the help of Build datatable - dtMerged then use an assign activity: left: dtMerged right (From x in Enumerable.Range(0, dt1.Rows.Count) Let ra = new Object(){dt1.Rows(x)(0),dt2.Rows(x)(0),dt3.Rows(x)(0)} Select dtMerged.Rows.Add(ra)).CopyToDataTAble Feel free to use ColName instead of ColIndex in case ofg the datatables will have different row counts we can adopt also have a look here:

[RaduNRV (Radu Valentin Neacsu)]

Hey @Palaniyappan

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

[Palaniyappan (Forum Leader)]

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

@Palaniyappan

1. 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

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

@Palaniyappan

3. Now use a ADD DATACOLUMN activity and pass datatable as dt1 and column property as dt2.Columns(0)
4. 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

[RaduNRV (Radu Valentin Neacsu)]

Did what you said but it looks like this:

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

[RaduNRV (Radu Valentin Neacsu)]

tried it but it gives me this:

[RaduNRV (Radu Valentin Neacsu)]

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