Joining DataTables created using Read Range

Help
, ,
1

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

Thanks

2

I have a similar query. Request help on this.

Thanks

3

@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

Thanks

4

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

5

Can you share the expected output?

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

and

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

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

Regards…!!
Aksh

1 Like
6

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

Can you suggest

7

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

8

Hey

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

Regards…!!
Aksh

1 Like
9

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
10

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

11

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

12

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