Join Two DataTables

datatable

#1

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?


#2

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)

  1. 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.


#3

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.


#4

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


#5

You can use Merge Datatable Activity here :slight_smile:

For your reference:

Regards…!!
Aksh


#6

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.


#7

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


#8

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?


#9

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


#10

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


#11

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?


#12

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


#13

@tobor Check the attached solution. Hope it helps.

JoinTables.xaml (10.4 KB)


How to merge three datatables together and save the result in an excel sheet
Deleting rows from a sheet after filtering
#14

That’s great. Thank you @vvadiya.

Thank you also to @aksh1yadav


#15

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?


#16

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.


#17

Can you share your workflow?


#18

PM’d it to you


#19

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)


#20

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