Joining DataTables created using Read Range

datatable
excel
activities

#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


#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


#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


#10

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