Comparing values and adding data to one data table

Help Studio
, ,
1

Hi,
I have 2 datatables containing data where one column of each datatable has common value to compare.
I need to compare both datatables based on both columns and paste the data from datatable2 to datatable 1
Here I have attached the excels containing data
DT1.xlsx (9.0 KB)
Dt2.xlsx (9.1 KB)
In Dt1 column Serial check needs to compared with column Serial of DT2 and if match is found than add data from Dt2 into Dt1
Output Sheet is as follows:
Result Sheet.xlsx (9.3 KB)
Linq query would be useful.

1 Like
2

Hi @Kunal_Jain

- Assign -> ResultDatatable = Datatable1.AsEnumerable.Where(Function(x) Datatable2.AsEnumerable.Any(Function(y) y("Serial").toString = x("Serial").toString)).CopyToDataTable

Hope it helps!!

3

@Kunal_Jain

Hi,

Use the Invoke Code activity in UiPath to write custom code for performing the comparison and data copying. Inside the activity, you can write VB.NET code to execute a LINQ query for the comparison and data copying.

’ Convert DataTables to enumerable lists for comparison
Dim dt1List As List(Of DataRow) = dt1.AsEnumerable().ToList()
Dim dt2List As List(Of DataRow) = dt2.AsEnumerable().ToList()

’ Perform the comparison and copy data using LINQ query
Dim queryResult = From row1 In dt1List
Join row2 In dt2List On row1(“Serial”) Equals row2(“Serial”)
Select row1, row2

’ Iterate through the query result and copy data from dt2 to dt1
For Each result In queryResult
result.row1(“ColumnToUpdate”) = result.row2(“ColumnToCopy”)
’ Repeat the above line for each column you want to copy from dt2 to dt1
Next

  1. After executing the code snippet, the data from “dt2” will be copied to “dt1” based on the common column values.
  2. Optionally, you can write the updated DataTable (“dt1”) back to an Excel file or any other destination using the “Write Range” activity.
4

Hi @mkankatala
This is not helping.
Actually it is not giving the desired output as I have mentioned before.

5

Hi @rlgandu
Can you help me with the code please?

7

@supermanPunch @Gokul001 @ushu @kirankumar.mahanthi1 @Palaniyappan @Sudharsan_Ka @Anil_G
Can you please suggest any approach?

8

Hi @Ananthu can you suggest something please?

9

The below xaml will add data row from dt2 to dt1 if match is found
Sequence2.xaml (9.8 KB)

Initially dt1.clone will add the dt1 headers to final datatable and then using a for each the dt2 column headers are added to the final datatable. Then using the linq query given below we add the rows matching to final datatable

(From d1 In dt1.AsEnumerable
Join d2 In dt2.AsEnumerable
On d1(“Serial Check”).ToString Equals d2(“Serial”).ToString
Select dtFinal.Rows.Add(d1(0),d1(1),d1(2),d2(1),d2(2),d2(3))).CopyToDataTable

10

Hi @Quenton_Wayne_Rebello
But the result sheet is missing unmatched values.
I have attached required result sheet for the reference.
Result Sheet.xlsx (9.3 KB)

11

To get unmatched values from dt1 in final datatable we can use the below code

(From d1 In dt1.AsEnumerable
Group Join d2 In dt2.AsEnumerable On d1(0).ToString Equals d2(0).ToString Into serialGroup = Group
From d2 In serialGroup.DefaultIfEmpty()
Select rowNeeded = {d1(0),d1(1),d1(2),If(d2 Is Nothing, Nothing, d2(1)),If(d2 Is Nothing, Nothing, d2(2)),If(d2 Is Nothing, Nothing, d2(3))}
Select dtFinal.Rows.Add(rowNeeded)).CopyToDataTable

12

@Quenton_Wayne_Rebello
Will it give both the matched and unmatched values on one go?
As the result sheet mentioned above?

13

yes it will give both matched and unmatched as per the sheet given

1 Like
14

@Kunal_Jain, If the solution works could you kindly mark it as solved :grinning:

15

@Kunal_Jain
Please refer below link to mark a post as solution

16

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.