Join Two DataTables

Yeah i have mentioned it in the post already if you have different then you can use For-each construct or For-each row activity and add new data to a new datatable using Add Data row activity.

You means if suppose same email field found in both then just skip that row or if not present then just add it ? right?

Regards…!!
Aksh

No. I don’t want to merge the tables, I want to join them. For example consider these 2 tables:

table 1:
Index,First_Name
1,john
2,james
3,fred
4,alan

table 2
Index,Second_Name
1,smith
2,jones
3,hunter
4,evans

I want to create table 3:

Index,First_Name,Second_Name
1,john,smith
2,james,jones
3,fred,hunter
4,alan,evans

Can this be done using merge?

No Merge will create the second row if you will use index also on datatable so it will treat as a primary key.

so you can get some idea from here:

let me know if still face some issues :slight_smile:

Regards…!!
Aksh

Thank you. I will look at the example you sent.

1 Like

Hi Aksh. Your example does not join the tables based on a common field between table 1 and 2. The output is:

Name City Country
abc xxx abcd
def yyy efgh
xx xxx
pp pppp
sss sssss

However, there is no logical join explaining why city xxx is in country abcd.

Alex Vaslie’s solution is the best but I need help understanding it. Can you create an example in line with Alex’s solution?

That was just for your guidance :wink: using loop. :stuck_out_tongue:

Will do, Once will sit on my system… On Mobile. Can not use UiPath on it :stuck_out_tongue:

Regards…!!
Aksh

@tobor Check the attached solution. Hope it helps.

JoinTables.xaml (10.4 KB)

6 Likes

That’s great. Thank you @vvadiya.

Thank you also to @aksh1yadav

Having joined 2 tables, I now have some rows with blank fields (because the join field is only present in 1 table). I have tried to filter and remove rows based on the blank fields but this is not working. How can the output table best be filtered to include rows for which join data is available in both tables?

I’m working on something similar and I cannot seem to get this to work. I’ve tried both the default Merge DataTable activity and your Invoke Method version, and I am having the same issue in both cases - the rows from the second datatable are being appended rather than merged with the rows with the same primary key from the first datatable.

In my case, the datatables in question are being generated by the Excel Read Range activity to pull from two different worksheets, but I have confirmed that the Excel formatting is the same, and the columns are successfully merging where applicable, just not the rows with the same primary key.

Can you share your workflow?

PM’d it to you

Nice example.

2 improvements if anyone can tell me:

  1. When i declare the PrimaryKey; and so the key has already repeated values on the column. How can i previously detect and delete them (with a rule like keeping the first or the more fullfilled row) (I was planning a double for to check everything, but to hard with plenty of rows)

  2. I whant to do a LEFT join, so if the 2nd Datatable has extra values of keys that doesn’t appear on the 1st DT, don’t include that rows on (i was planning to iterate and clean those, but maybe you have a better efficient idea. Also a DT.Select could be)

Hi all, is there an efficient way to outer join 2 data tables (without looping through the datatables)?

For example consider below 2 datatables as input:

dtInputOne
ID,Value
1,“John”
2,“Ringo”
3,“Paul”
4,“George”

dtInputTwo
ID,Value
1,“John”
2,“Ringo”
3,“Paul”
4,“George”
5,“Yoko”

Based on these, how can I obtain a third datatable “dtOutput”?

dtOutput
ID,Value
5,“Yoko”

It appears in UiPath Studio 2018.3.0 Beta the Join Data Tables does not contain an outer join option.
Thanks!

Kind regards,

Kris

hi @krisleunis,
You can compare two dataTables and get extras or I can say unmatched records by using this:

dt3 = dt2.AsEnumerable().Where(function(f) Not dt1.AsEnumerable().Select(function(i) i.Field(Of Int32)(“ID”).ToString).Any(function(i2) i2 = f.Field(Of Int32)(“ID”).ToString)).CopyToDataTable()

e.g.
dt1 → has 3 records,
dt2 → has 4 records (1 extra).

here’s my workflow:
Main.xaml (9.6 KB)

I hope this will help you. :slight_smile:

Hi @samir,

Many thanks for your help.
Your workflow indeed works for the example I provided.
However, it does not work in the scenario where both datatables contain unique records. :cry:

Please find the example below:

dtInputOne
ID,Value
1,“John”
6,“Albert”
2,“Ringo”
3,“Paul”
4,“George”

dtInputTwo
ID,Value
1,“John”
2,“Ringo”
3,“Paul”
4,“George”
5,“Yoko”

Based on these, how can I obtain?

dtOutput
ID,Value
6,“Albert”
5,“Yoko”

P.S. This is a feature I expected the Join Data Tables activity to have the JoinType “Outer”

Hello vvaidya!! Thanks for your solution. I need to ask how to join 2 different sheets of Single Excel Workbook into single excel sheet. I want to merge two excel sheet into one sheet. I have excel workbook SampleClientsData.xlsx. It contains 2 different sheets first is “Client_Members_Sheet” and another is “Sales_Member_Sheet”. I want to get Sales_Member_Sheet data into Clients_Members_Sheet without blank columns and proper data. I tried using Merge Datatable. But it is not working. It is adding extra 3 blank columns into excel sheet “Clients_Members_Sheet”.
I am sending you the flow and Screenshots and Excel sheet. Please suggest me proper solution to this problem.

MergingTwoExistingDatable.xaml (7.9 KB)
SampleClientsData.xlsx (11.9 KB)

PFA
Regards
Rohit Lanjewar

Hello Aksh!! Thanks for your solution. I need to ask how to join 2 different sheets of Single Excel Workbook into single excel sheet. I want to merge two excel sheet into one sheet. I have excel workbook SampleClientsData.xlsx. It contains 2 different sheets first is “Client_Members_Sheet” and another is “Sales_Member_Sheet”. I want to get Sales_Member_Sheet data into Clients_Members_Sheet without blank columns and proper data. I tried using Merge Datatable. But it is not working. It is adding extra 3 blank columns into excel sheet “Clients_Members_Sheet”.
I am sending you the flow and Screenshots and Excel sheet. Please suggest me proper solution to this problem.

MergingTwoExistingDatable.xaml (7.9 KB)
SampleClientsData.xlsx (11.9 KB)

PFA
Regards
Rohit Lanjewar

Hello Samir!! Thanks for your solution. I need to ask how to join 2 different sheets of Single Excel Workbook into single excel sheet. I want to merge two excel sheet into one sheet. I have excel workbook SampleClientsData.xlsx. It contains 2 different sheets first is “Client_Members_Sheet” and another is “Sales_Member_Sheet”. I want to get Sales_Member_Sheet data into Clients_Members_Sheet without blank columns and proper data. I tried using Merge Datatable. But it is not working. It is adding extra 3 blank columns into excel sheet “Clients_Members_Sheet”.
I am sending you the flow and Screenshots and Excel sheet. Please suggest me proper solution to this problem.

MergingTwoExistingDatable.xaml (7.9 KB)
SampleClientsData.xlsx (11.9 KB)

PFA
Regards
Rohit Lanjewar

Hi,

If you are still looking for a solution to write selects like in SQL please see the example below in LINQ.

(From s In DTConsolidatedWithIntervals.AsEnumerable()
Join r In DTDatesWithIntervals.AsEnumerable()
On s(“Station ID”).tostring Equals r(“Station ID”).tostring
Where (s(“Date Start”).tostring >= r(“Date_From”).tostring And s(“Date Start”).tostring <=r(“Date_To”).tostring) Or (s(“Date Start”).tostring<r(“Date_To”).tostring And s(“Date End”).tostring>r(“Date_To”).tostring) Or (s(“Date End”).tostring>=r(“Date_From”).tostring And s(“Date End”).tostring<=r(“Date_To”).tostring)
Select DTCentralised.LoadDataRow(New Object() {r(“Date”),s(“User ID”),s(“Car registration”),s(“Station ID”),s(“Date Start”),s(“Date End”),r(“Date_From”),r(“Date_To”),If(r(“Valid From”).tostring=“”,Nothing,r(“Valid From”)),If(r(“Valid To”).tostring=“”,Nothing,r(“Valid To”).tostring),r(“Price per hour (RON)”),r(“Discount per hour (RON)”)},False)).tolist()

I don’t have an example with outer but I think the LINQ has this functionality.

Happy Development
CristianZ

1 Like