Compare Datatables one specific column with other specific Column and Get Matched and Not Matched Records`

Learning Hub Academy Feedback
43

Hi! I have a problem. I have two datatables with MemberId column in both. In Datatable2 there’s a column Status. I want to add this Status column in Datatable1 by comparing MemberId columns of both and sort out matching records. The corresponding Status records(which have matching MemberId records in both) should be added in Datatable1 using uipath. Can anyone please guide how can this be done?

Thanks in advance!

1 Like
44

Hi Aksh, this is great, thanks for sharing :slight_smile: Would it be possible to use a similar formula to find partial string matches between sheets? So it would look in a column in DT1 to see if the string contains any of the strings in a column in DT2. e.g. “brand product type description name” in DT1 contains “product type” in DT2. I’m hoping for it to return the string and the cell address of the matched cell in DT2.

1 Like
45

Hi,

I’m trying to compare two excel sheets and get the matched records in 3rd excel based on multiple columns. Can you pls share the xaml file for getting the matched records in excel by comparing two datatables based on multiple columns.

TIA

46

How can we use the above code for multiple columns ? i.e output should be one data table but we require to match multiple columns based on employee code in both files?

47

Hey @jakegill, did you get any breakthrough with you issue related to this post??

48

Hellow @aksh1yadav even i tried you code for my issue, i find error like Assign: The source contains no DataRows.

I have to do keyword search from one excel to another, in one excel i have column where there is only keywords and in another i have records and i have to search the keyword contains on Description column

image
image

49

Hi @indrajit.shah, yes indeed, here’s the thread: Compare two lists of strings for partial matches without for each loop

50

Sorry to pull up an old post here, but I was also wondering if it’s possible to make this accept an empty data table, in the event no rows meet comparison criteria. Right now I have an if statement with row count:

if In_DataTable1.AsEnumerable().Where(function(row) Not In_DataTable2.AsEnumerable().Select(function(r) r.Field(Of Int32)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Int32)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable().Rows.Count > 0

Then dtResults = In_DataTable1.AsEnumerable().Where(function(row) Not In_DataTable2.AsEnumerable().Select(function(r) r.Field(Of Int32)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Int32)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable()

Else dtResults = “No new items”

But I have a lot of data tables to go through, and I’m wondering if there is a way to embed this in the initial assign @aksh1yadav built. Let me know if you came up with something, thank you! @sai_prasanth

51

@Hillary Can you explain the process you want to Achieve in Detail ?

52

Hey @sai_prasanth

.CopyToDatatable() does not accept zero rows to deal with. so to avoid such error you can do take first rows collection in datarow[ ] and then use if condition to check count of it. if it is having count then you can directly convert it into datatable even at the time of Excel file writing using .CopyToDatatable()

Regards…!!
Aksh

2 Likes
53

Often, when I am pulling unmatched rows between two data tables using the comparative code above, all rows will match and instead of just continuing the process, it will fail because the data table cannot be empty. I’ve resolved this by adding an “empty” row to one table each time I compare, and run it through an if statement. If my data table only has 1 row (my added empty row), I know I have no results and I pass to the next process. I was just wondering if I could add some accept null to the compare code, but I haven’t been able to come up with anything. Let me know if you have any handy work arounds for this!

54

@Hillary Yes, Just Like @aksh1yadav Suggested you need to handle that error While using CopyToDatatable

  1. Create an array of Datarow variable, say DTrowArray

  2. In Assign First use this Statement , DTrowArray = In_DataTable1.AsEnumerable().Where(function(row) Not In_DataTable2.AsEnumerable().Select(function® r.Field(Of Int32)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Int32)(In_DT1_ColName_To_Match.ToString))).ToArray

  3. In an If Condition Activity, Use this Condition
    DTrowArray.Count>0
    then
    in Assign use , YourDatatableVarable = DTrowArray.CopyToDatatable
    else
    Use This Statement :
    YourDatatableVariable = YourDatatableVariable.Clone

Can you check with these Steps , But the Logic Differs a bit in Assignment in the else Condition based on your requirement :sweat_smile:

3 Likes
55

@Hillary. As sugeested by @supermanPunch is the solution i have used in my case earlier. One more approach is using the Try catch activity, use Assign in Try part and in the catch part
Use This Statement :
YourDatatableVariable = YourDatatableVariable.Clone

2 Likes
56

I’ll try out the array, thank you @aksh1yadav, @supermanPunch, and @sai_prasanth!

2 Likes
58

i want to compare column from one excel sheet to another excel sheet column substring and find missing or unmatched values
please help me out
here my excel sheets and columns highlighted in yellow colourBRS statement.xlsx (10.7 KB) textexcel.xlsx (8.9 KB)