Join Two DataTables

[UiRobot (UiPath Robot)]

Trevor Napier
June 27, 2016 21:44 NONE
So I know going through VB/C# you can use LINQ to join two tables IE

Dim query = From tl1 In InputTableOne.AsEnumerable() _
Join tl2 In InputTableTwo.AsEnumerable() _
On tl1.Field(Of Integer)(“Customer_ID”) Equals _
tl2.Field(Of Integer)(“Customer_ID”) _
Select New With _
{ _
.ID = tl1.Field(Of Integer)(“Customer_ID”), _
.Customer_Name = tl1.Field(Of String)(“Customer_Name”), _
.Order_ID = tl2.Field(Of Integer)(“Order_ID”), _
.Order_Item = tl2.Field(Of String)(“Item”) _
};

Then loop over the results to add/build the joined table.

Is this possible within UiPath? I have tried several ways but always seem to hit type errors at the loop. Am I missing some other way to do this?

[UiRobot (UiPath Robot)]

Alex Vasile June 30, 2016 02:55 Official comment
Hello Trevor,

Let’s assume that you have two DataTables. In order to do an inner join you can follow these steps:

1. Declare a third DataTable and into an Assign activity copy the structure from one of the first two using the Clone method.

2. Use two For each row activities to loop through the DataTables.

3. With an If activity, check this condition:

Enumerable.SequenceEqual(row.ItemArray,row2.ItemArray)

4. If the condition is true, then you can use an Invoke Method activity where you need to provide three properties:

-TargetObject: DataTable3.Rows

-MethodName: Add

-Parameters: An In parameter of Type:Object and Value: row.ItemArray

I hope these instructions will help you.

[UiRobot (UiPath Robot)]

Trevor Napier July 07, 2016 15:57
I ended up solving this problem by creating a plug in to join tables based on shared columns/keys.

I did try this method as well which will give a final result set of rows that were the same in both tables, but does not actually perform a join of the data.

[tobor (Tobor Atad)]

Hi Alex. I am trying to use this method to join 2 tables but am not familiar with “loop through”. Is it possible to explain this further and, even better, provide a simple example xaml file.

Thanks in advance

[aksh1yadav (AKSHAY YADAV)]

@tobor

I am trying to use this method to join 2 tables

You can use Merge Datatable Activity here

For your reference:

How to merge two data tables

hey @Sunitha_Bist we can merge two tables into a single table but you should know some points before merging: **If the number of columns do not match the second table* When the number of columns do not match the second table then it creates blank columns for the table for the column(s) that do not match, as in the following: If the data type of a column does not match the second table The data types must match. If the column names are the same and if the column name in both tables are th…

Regards…!!
Aksh

[tobor (Tobor Atad)]

Hi Aksh. I am looking to join 2 tables to extend the data in each row using a field common to each table (similar to vlookup). Is that possible using merge? I thought that merge would take 2 tables with the same (or similar) field structures and add the rows, e.g. one table with 10 rows and the other table with 15 would output a new table with 25 rows.

[aksh1yadav (AKSHAY YADAV)]

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.

@tobor

. I am looking to join 2 tables to extend the data in each row using a field common to each table

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

[tobor (Tobor Atad)]

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?

[aksh1yadav (AKSHAY YADAV)]

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:

How to merge two data tables

@Sunitha_Bist My idea is similar to the attached code. You can tweak it a bit as per your requirement. merge.zip (19.9 KB)

let me know if still face some issues

Regards…!!
Aksh

[tobor (Tobor Atad)]

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

[tobor (Tobor Atad)]

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?

[aksh1yadav (AKSHAY YADAV)]

That was just for your guidance using loop.

@tobor

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

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

Regards…!!
Aksh

[vvaidya (Vinay Vaidya)]

@tobor Check the attached solution. Hope it helps.

JoinTables.xaml (10.4 KB)

[tobor (Tobor Atad)]

That’s great. Thank you @vvadiya.

Thank you also to @aksh1yadav

[tobor (Tobor Atad)]

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?

[jcarr79 (John Carr)]

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.

[vvaidya (Vinay Vaidya)]

Can you share your workflow?

[jcarr79 (John Carr)]

PM’d it to you

[GERMAN_LARRAIN]

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)

[krisleunis]

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