Merge 2 data table side by side

[SunnyJha]

I have 2 datatable both have unique columns so there’s no common column between them. i want to merge the 2 datatable having columns side by side like lets say dt1 has 3 columns and dt2 has 2 columns, so merged data should look like columnA, B, C, D, E.

[Krishna_Raj]

@SunnyJha

1. Read Range (File path for dt1) // Output: dt1
2. Read Range (File path for dt2) // Output: dt2
3. Merge Data Table:
   • DataTable1: dt1
   • DataTable2: dt2
   • Join Type: Full Outer
     // Output: mergedDataTable
4. For each column (column) in dt1
   If Not mergedDataTable.Columns.Contains(column.ColumnName)
   Add Data Column:
   - DataTable: mergedDataTable
   - Column Name: column.ColumnName
   - DataType: column.DataType
5. For each column (column) in dt2
   If Not mergedDataTable.Columns.Contains(column.ColumnName)
   Add Data Column:
   - DataTable: mergedDataTable
   - Column Name: column.ColumnName
   - DataType: column.DataType
6. Reorder Columns:
   • DataTable: mergedDataTable
   • Order: {columnA, columnB, columnC, columnD, columnE}
7. Write Range (Output file path) // Input: mergedDataTable

[mkankatala (Mahesh Kankatala)]

Hi @SunnyJha

You can use the Join datatable activity, if there is any column which will be same in both datatables then this activity will works.

→ Drag and drop the Join Datatable activity.
→ Click on Join Wizard button, Pass the DT1 Variable in Input Data Table 1 and DT2 Variable in Input Data Table 2 field.
→ In the Column Table 1 and Column Table 2 give the column names which are common in both datatables.
→ In the Operation dropdown select the = option.
→ In the Join Type select the Inner option.

Check the below image for better understanding,

Hope it helps!!

[SunnyJha]

As I mentioned there is no common column so Join datatable or merge datatable activity is not working.

[SunnyJha]

I saw this on chatgpt, was a bit confusing so asked here. I will try this out though, thanks!

[mkankatala (Mahesh Kankatala)]

If possible share the Input data and expected output, then we can easily understand and provide the solution for your query… @SunnyJha

Hope you understand!!

[SunnyJha]

Input dt1 and dt2:

Output Merged:

Data should appear side by side.

[PVSneha_Nair (Sneha Vijayan)]

Hi @SunnyJha ,
you can use LINQ to join two tables which have no common column.

->First read the excels to data tables ,dt1 & dt2

->Can use linq to add the joined data table value to data table dt3 using assign activity

(From row1 In dt1.AsEnumerable()
From row2 In dt2.AsEnumerable()
Select dt3.Rows.Add(row1.ItemArray.Concat(row2.ItemArray).ToArray())).CopyToDataTable()

• From row1 In dt1.AsEnumerable(): This part iterates over each row in dt1.
• From row2 In dt2.AsEnumerable(): This part iterates over each row in dt2. Since it’s nested within the previous loop, it means that for each row in dt1, all rows in dt2 are traversed.
• Select dt3.Rows.Add(row1.ItemArray.Concat(row2.ItemArray).ToArray()): For each combination of rows from dt1 and dt2, this part creates a new row in dt3. It concatenates the values of the current rows (row1 and row2) into a single array, and then adds this array as a new row to dt3.
• CopyToDataTable(): This method converts the result of the LINQ query (which is an IEnumerable) to a DataTable (dt3).

[mkankatala (Mahesh Kankatala)]

Okay @SunnyJha

You can try the below steps,
→ Use Read range workbook activity to read the dt1 sheet and store in a datatable called DT1.
→ Use Write range workbook activity to write the DT1 to merge sheet in same excel.
→ Use another Read range workbook activity to read the dt2 sheet and store in a datatable called DT2.
→ Use another write range workbook activity to write the DT2 to merge sheet, in the cell field give the C1

Check the below image for better understanding,

dt1 -

dt2 -

Merge -

Hope it helps!!

[Jayesh_678 (Jayesh)]

Hi @SunnyJha

Input:-
dt1:-

dt2:-

Output:-
dt1-

Xaml file:-
merge dt side by side forum.zip (3.7 KB)

If this works for you, please mark this as a solution, so others can refer the same.
Thanks

[SunnyJha]

I am getting object reference not set to an instance of an object error in the assign activity where linq query is written. All 3 datatables used are defined, what should I check for?

[PVSneha_Nair (Sneha Vijayan)]

Need to initialize the data table dt3.

[SunnyJha]

Hi sneha,
Thanks for responding!
The example that I showed is much simpler than the usecase I am figuring out.
I have a file in which I am picking up some columns and then merging them as a single datatable.
I would need the solution to be more scalable and dynamic rather than doing a build datatable(which I figured out that I was missing the column names).
The column names needs to be picked from the dt1 and dt2 and should be a part of dt3.

[PVSneha_Nair (Sneha Vijayan)]

->can use invoke code to add columns from dt1 and dt2 to dt3
Assign: dt3 = New DataTable()
For Each col1 In dt1.Columns.Cast(Of DataColumn)()
dt3.Columns.Add(New DataColumn(col1.ColumnName, col1.DataType))
Next
For Each col2 In dt2.Columns.Cast(Of DataColumn)()
If Not dt3.Columns.Contains(col2.ColumnName) Then
dt3.Columns.Add(New DataColumn(col2.ColumnName, col2.DataType))
End If
Next

->then can use linq to join

[ppr (Peter Preuss)]

How to merge the excel table properly

@Vi_ciel have a look on following flow, supporting different rows count e.g. on dt1, dt2 [grafik] With datatable merge details: [grafik] input / output: [grafik] Kindly note: in case of multiple datatables will have the same column name, it has to be handled in advance by making the col names unique find starter help here: SideBySideMerge_AlsoDifferentRCnts.xaml (10.8 KB)

or we just do it on excel base with a range offset