Another Method for Two DataTable Merge

Help Studio
,
1

Dear Forum,

I have two Excel files, and I’m using the “Read Range Workbook” activity to retrieve data tables from them.

Now, I’m wondering how I can merge these two tables efficiently.

My initial approach involves using a “For Each Row” loop on the data tables and employing the “Add DataRow” activity to merge them.

However, I’m interested in exploring alternative methods that might be more effective.

Any suggestions would be greatly appreciated. Thank you!

2

Hi @FINNNNNNNN

Can you please show us the structure of the Data Tables?
Are the column names same for both of them?
If so you can use Merge DataTable activity directly

Thanks

Happy Automation! :slight_smile:

1 Like
3

elaborate the merge needs e.g. with sample data and expected output sample

instead of we can check for:

  • write range / append range combinations
  • merge datatable

or custom merges e.g. with LINQ or Essential Activities

1 Like
4

hi @FINNNNNNNN

you can use the below code for merge data table using linq query.

Imports System.Linq

’ Assuming dt1 and dt2 are the two DataTables you want to merge based on a common column
’ Assuming the common column name is “ID”

’ Merge dt2 into dt1 based on common column “ID”
Dim mergedDataTable = (From row1 In dt1.AsEnumerable()
Join row2 In dt2.AsEnumerable()
On row1.Field(Of Integer)(“ID”) Equals row2.Field(Of Integer)(“ID”)
Select dt1.Clone().Rows.Add(row1.ItemArray.Concat(row2.ItemArray).ToArray())).CopyToDataTable()

please use the above code in INVOKE CODE Activity

Hope it helps!!!

1 Like
5

CardPayments.xlsx (9.4 KB)
CashPayments.xlsx (8.2 KB)

I want to put the Cash Data into the Card Data Table,
these have the same column name.

6

if still the column names are identical. can you please try with the below code.

Dim query =
From e In dtEmployees.AsEnumerable()
Join s In dtSalaries.AsEnumerable()
On e.Field(Of String)(“EmployeeID”) Equals s.Field(Of String)(“EmployeeID”)
Select dtJoined.LoadDataRow(New Object() {
e(“EmployeeID”),
e(“Name”),
s(“Salary”)
}, False)

Dim joinedData = query.CopyToDataTable()

7

One Excel base:

  • Use Append Range and add second Excel to the first Excel or to a third excel

Or:

as both options has been mentioned above already

8

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