How to join 2 datatables on multiple fields

Help
1

I have 2 tables without primary keys. I am trying to join them based on 7 different columns being equal to each other. There are more columns in dt2 than there are in dt1

So it’d be similar to:

Select dt1.*, dt2.*
Join dt2 on dt1.col1-7 = dt2.col1-7

Anyone have an idea how to do this in UiPath? I tried using merge table activity, but that just appended the rows (similar to union all) rather than joining the tables.

2

Does the 7 common columns have same column names?

3

yes they do - I actually got the 7 by doing a group by function. I’m now trying to bring the results back to the original datatable but with the matching extra columns

4

If I understand your question correctly, you are expecting 14 columns (with 7 duplicate column names) in the Output DataTable?

5

Sorry, probably wasn’t the best explanation.

dt1 has 9 columns. dt2 has 29 columns. 7 columns are shared between the two tables. I wanted to join on the dt1 and dt2 on those shared columns where dt1 = dt2

So the new dt would have 31 columns total (7 shared, 2 from dt1, 22 from dt2)

6

Understood, don’t you think you will end up having duplicate column names which is not allowed in DataTable?

Off the top of my head, you could do something like below liqn queries will give you 2 datatables. Then you can do nested for each and merge respective rows and pass it into a new DataTable.

String.Join(",",table1row1.ItemArray)+","+String.Join(",",table2row1.ItemArray)

(From a In dt1.AsEnumerable() Join b In dt2.AsEnumerable() On a("c1").ToString() Equals b("c1").ToString() and a("c2").ToString() Equals b("c2").ToString() and a("c3").ToString() Equals b("c3").ToString() and a("c4").ToString() Equals b("c4").ToString() and a("c5").ToString() Equals b("c5").ToString() and a("c6").ToString() Equals b("c6").ToString() and a("c7").ToString() Equals b("c7").ToString() select a}).CopyToDataTable

(From a In dt1.AsEnumerable() Join b In dt2.AsEnumerable() On a("c1").ToString() Equals b("c1").ToString() and a("c2").ToString() Equals b("c2").ToString() and a("c3").ToString() Equals b("c3").ToString() and a("c4").ToString() Equals b("c4").ToString() and a("c5").ToString() Equals b("c5").ToString() and a("c6").ToString() Equals b("c6").ToString() and a("c7").ToString() Equals b("c7").ToString() select b}).CopyToDataTable

1 Like
7

I’m very new to linq, so I’m not sure. I know in SQL it wouldn’t be an issue as there wouldn’t be any of the same column names because I’m joining on any columns that have the same name.

In your examples I’m assuming “c1” “c2” etc are the column names?
And it seems like I could just do one of the From statements, right? I’m not sure what the string.join at the beginning is for, or the multiple From a in dt1… statements are for?

8

Yes, “c1”,“c2” are column names and you need to replace them with your columns. You have to use both Linq statements, 1st one returns rows from dt1 and 2nd one retruns rows from dt2. There is also a way to get both records using single query (select new {a,b}), but then the output will be IEnumerable(DataRow), it will be a bit more complicated to extract data.

There must be a better way, using String.Join I’m concatenating common rows from both the tables, this needs to be done within a nested for each loop of both data tables above. After concatenating you need to pass that into new datatable (using Add Data Row).

To avoid duplicate column names, build a database with 31 unique columns before passing the rows. I need to check if there is an easy way to concatenate data table columns (perhaps by cloning).

Hope it is clear now.

9

Thanks for the detailed post - it is a little confusing to me, but can hopefully work it out :slight_smile:

It seems like LINQ should have an easy way to do a simple inner join like this just once without having to do a for each within a for each, etc.

SQL would be as simple as:
Select dt1.*,dt2.*
From dt1
Inner Join dt2 On dt1.c1 = dt2.c1 AND dt1.c(n) = dt2.c(n)

where (n) represents column number (and I’d repeat for each column needed to match)

10

This is my idea, there should definitely be a better way. Hopefully it will point you in a direction until someone comes up with a better idea. Even I will think about it again. Also, I’m not sure what impact it will have on other data types. Good luck.

join.xaml (25.4 KB)

1 Like
11

¡Hi!, i have a similar issue.

I want to join 2 datatables but my cells cant be equals, maybe can contains part of other cell, for example

DataTable 1
image

DataTable 2
image

As you can see, in my second datatable the cell A2 and B3 contains the cell in first datatable A2 and A3 i want to generate my output like

image

¿How can i use Join for do this? Bless!

12

hi @inf_L
Use merge datatable activity

Thanks
Ashwin S