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.
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:
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.
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:
This will filter DataTable A to check if the current row from DataTable B exists in DataTable A.
Check Filtered Rows:
After filtering DataTable A, use an If activity to check if any rows are returned after filtering.
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.
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.
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?
NewDT=DT2.AsEnumerable().Where(Function(rowB) Not DT1.AsEnumerable().Any(Function(rowA) ColumnNames.All(Function(columnName) rowA(columnName).Equals(rowB(columnName))))).CopyToDataTable()
