Joining DataTables created using Read Range

Hi All,

My requirement is to to Join two DataTables created using Read Range (Excel) through a common primary key present in both tables.

I have tried to assign primary key using the following approach

datatable1.PrimaryKey = New DataColumn() {datatable1.Columns(“Column Name”)}
datatable2.PrimaryKey = New DataColumn() {datatable2.Columns(“Column Name”)}

And then used the Invoke Method using Merge

But, the resulting output seems to return only a merged table instead of a join

Note: the primary key column is of string data type and are properly trimmed

Can you please help me with this join requirement


I have a similar query. Request help on this.


@vvaidya I saw a similar query addressed to you on the same topic, can you provide a solution for the same, will be really helpful


Do you think this will work for you?
(This needs correction, will get back to you when I can)

dtJoin = (From a In dt1.AsEnumerable() Join b In dt2.AsEnumerable() On a(primKey).ToString() Equals b(primKey).ToString() select a).CopyToDataTable

Can you share the expected output?

Well i guess it should work with Merge like this: @vvaidya already share such sample before i guess.


This will return things based on first datatble columns :slight_smile:

Try to see this: dt join.xaml (10.5 KB)


1 Like

Thanks for the reply the Join works but I need fields from both a and b tables

Can you suggest

Hi @aksh1yadav the merge invoke method is working only with datatables created using build data table and not on ones using read range from excel


Its working for me…
check it out… (15.4 KB)


1 Like

If you are still looking for a alternative to merge solution (not sure why it doesn’t work for you), try this way.

dtJoin = (From a In dt1.AsEnumerable() Join b In dt2.AsEnumerable() On a.Field(Of String)("ID") Equals b.Field(Of String)("ID") Select dtJoin.LoadDataRow(New Object() {a.Field(Of String)("ID"), a.Field(Of String)("Name"), b.Field(Of String)("Gender")}, False)).CopyToDataTable

1 Like

@vvaidya @aksh1yadav Thanks for the immediate response, have tried both the approaches suggested and it is working fine.

@vvaidya, how to achieve remove matching rows in two different datatable

Hey I am getting a error in the above query,can you please help.
Assign : Unable to cast object of type ‘System.Double’ to type 'System.String

I am changing the names of column according to my requirement,and one column contains date format and other contains values like 1.3,1.4,1.9 and so on,but why it is giving error if I am taking all the columns in string format.
Please suggest

1 Like