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

Hello @aksh1yadav,

I have used your Code to Compare 2 Datatable Columns for “Non-Matching Records” but its taking ~14 Minutes of time to get the result.

In my source datatable has around 50000 rows and in my target datatable has almost 60000 rows which are used in comparisons process.

Could you please optimise your code/query so that it can take lesser time to get the result.

Your help would be very appreciable for me…

Regards,
Nidhees

Hi guys,

I am trying to get the value a particular column from a selected row of a data table?

Initially i tried this syntax: x = Datatable1.Select("[Column_Name] =’" + Column_Value.ToString+"’")(0)(108)

But the problem in it is column index(108) is changing, how to make it dynamic?

Please provide me the syntax for this condition

Regards
Sreekanth Reddy

bro in this how to get the both datatble column details

Hi Community,

Thanks Akish for the snippet, how do i obtain greater than (>) values or less than (<) values?
i see you have options for Matched and Not Matched (Not)

1 Like

Hi @aksh1yadav you have just used the assign activity in your sample workflow. i tried using your activity for getting not matched records .

Consider there is zero returned for the condition then it is throwing up an error like source contains no datarow.

Do you have solution how to fix this and make the datatable accept zero datarows?

Thank you so much for this @aksh1yadav ! Saved so much time and i’m sure it has for hundreds of others too!

2 Likes

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

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

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

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?

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

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

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

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

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

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

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!

@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

@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

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

2 Likes