Comparing rows in data table


How do I compare and delete rows in data table in the situation like this:

I have a data table A which consist of a row example:

Name Surname Job Arrested?
Mark Jason Accountant Yes
Amy Marks Nurse Yes
Jason Derulo Singer Yes

and now I have another data table B that consist only of the first three columns so:

Name Surname Job
Mark Jason Accountant
Amy Marks Nurse
Jason Derulo Singer
Jack Shephard Doctor

So now I need to compare if the rows in data table are included in the data table B

In this case Jack Shepard is not on a list so I would like to have him in a new seperate data table (he would be the only result in this case but if there were more names that would not be in the data table A there would be ofc more in the new data table.

Normally I would use use delete duplicates however the columns are different because first one contains “Arrested” and the second one will not.

Any ideas?

Hi @marcin.chowaniec

Try this:
rowsInBNotInA = DataTableB.AsEnumerable().Except(DataTableA.AsEnumerable(), System.Data.DataRowComparer.Default).CopyToDataTable()

Hi @marcin.chowaniec

Check out the expression

Use a assign activity like this

For Common Values

dt = dt1.AsEnumerable().Intersect(dt2.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable

For Uncommon Values

dt = dt1.AsEnumerable().Except(dt2.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable


Hii @marcin.chowaniec

Try this Linq Query

(From row In inputDataTable.AsEnumerable()
 Where Not (row("ColumnName").ToString = "SomeValue")
 Select row).CopyToDataTable


Something is wrong in rowsInBNotInA I’m getting the same result as DataTableA

Hi @marcin.chowaniec ,

Can you please try below code,

DT3 = DT2.AsEnumerable().Where(Function(rowB) Not DT1.AsEnumerable().Any(Function(rowA) rowA(“name”).Equals(rowB(“name”)))).CopyToDataTable()

Sandhiya P

It works I have misplaced Data Table A with Data Table B Thanks!

1 Like

Hi @supriya117 ,

This code giving me all the values in Datatable B.

Not sure am I doing anything wrong, can you please help me on this.

Sandhiya P


Check once if you correctly pass the datatable names or not.

rowsInBNotInA = DataTableB.AsEnumerable().Except(DataTableA.AsEnumerable(), System.Data.DataRowComparer.Default).CopyToDataTable()

Yeah I checked again and still something is wrong oh no.

I need to take a closer look on this.

Hi @supriya117 @marcin.chowaniec ,

Yes when I tried the code which I mentioned, it’s working for me.

Can you please check and update which one working as expected.

Sandhiya P

Yes I’m trying your code at the moment.

Weird error:

In UiPath, you can achieve this task by using the DataTable activities. Here are the steps to compare and delete rows in a DataTable in UiPath:

  1. Read Data Tables:
    Use the Read Range activity to read both DataTable A and DataTable B from your Excel or CSV files. This activity will store the data in DataTable variables.

  2. Compare and Filter Rows:
    Use the For Each Row activity to loop through DataTable B. Inside the loop, use the Filter Data Table activity to filter DataTable A based on the current row from DataTable B. You can set the filter condition based on the ‘Name’, ‘Surname’, and ‘Job’ columns.

    For example, using the following expression in the Filter Wizard:

    "[Name] = '" & row("Name").ToString & "' AND [Surname] = '" & row("Surname").ToString & "' AND [Job] = '" & row("Job").ToString & "'"

    This will filter DataTable A to check if the current row from DataTable B exists in DataTable A.

  3. Check Filtered Rows:
    After filtering DataTable A, use an If activity to check if any rows are returned after filtering.

  4. Delete Rows (Optional):
    If there are filtered rows in DataTable A, use the Remove Data Row activity within the Then block of the If activity to remove the rows from DataTable A that match the current row in DataTable B.

  5. Output (Optional):
    If you want to store the unmatched rows, you can use another DataTable variable to store them. Before removing the rows from DataTable A, add the rows to this new DataTable variable.

Here is a simple representation of the steps in UiPath:

Read Range (DataTable A)
Read Range (DataTable B)

For Each Row in DataTable B
    Filter DataTable A based on current row (Name, Surname, Job)
    If Filtered Rows Exist
        Add Filtered Rows to New DataTable (Optional)
        Remove Filtered Rows from DataTable A (Optional)
    End If
End For

Remember to adjust the column names and data types in the expressions based on your specific DataTable structure.

What shoud I put here?l

Update the Double Quotes in the expression and check it @marcin.chowaniec

Hi @marcin.chowaniec ,

Please give unique identifier column name. The code doesn’t throw any error for me,

Please let me know if you face any issues.

Sandhiya P

I understand, can I add three columns as unique modifiers names? how do I do that? the table I have is more complicated than name surname, I need to have three columns to compare to know if this is a duplicate or not, any chance you could help me with this?

Hi @marcin.chowaniec ,

First create array of list for column names.



NewDT=DT2.AsEnumerable().Where(Function(rowB) Not DT1.AsEnumerable().Any(Function(rowA) ColumnNames.All(Function(columnName) rowA(columnName).Equals(rowB(columnName))))).CopyToDataTable()

Sandhiya P

1 Like

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