Help to remove rows in datatable if they match value in a second datatable

[kkiw (Henry Wang)]

Hi,

I have a datatable which I use as a work queue. This datatable contains user IDs (io_dtInactiveUsers). However, some user IDs are supposed to be excluded from processing in the work queue. These user IDs which are supposed to be excluded can be found in a second datatable (dtExclusion).

The way I have built my automation is that for each row in io_dtInactiveUsers, I check if the user ID matches any of the rows in dtExclusion. If it does, then it sets the a boolean variable to True. This will remove the data row in io_dtInactiveUsers. Please see workflow attached in picture below:

However, I get the following error message:
“For Each Row: Collection was modified; enumeration operation might not execute.”

I suspect the datatable does not like to be adjusted with while it’s still looping.

The forum topics I have found so far have only been able to delete rows if they are some specific value. Would therefore be very glad if I would receive some help to remove the rows if it matches any of the values in the second datatable.

Many thanks!

[Gil_Silva]

Hello Henry,

You’re right, you shouldn’t modify a data table while you’re accessing their indexes.
There are multiple ways of achieving what you’re looking for, but keeping the logic you already built, you can simply have a 3rd data table, so if you want to keep a row, you add it to a new data table.

Let me know if it helped.

[OLAOLUWA_DARAMOLA]

Hi Henry,

You can explore using the UiPath Join datatable activity, if you specify the columns you want to match UiPath will return a datatable where the ones that match have values and the ones that don’t are null.

This is assuming the User IDs exist in the exact same way on both tables but in different columns.

Check out the documentation here

Join Data Tables

[Vishal_K]

Hi @kkiw

You can use the Assign activity with the following LINQ query

dtOutput=io_dtInactiveUsers.AsEnumerable().Where(Function(row) NOT dtExclusion.AsEnumerable().Any(Function(x) x(“ID”).ToString=row(“ID”).ToString)).CopyToDatatable;

• The dtOutput fetches data from io_dtInactiveUsers which are not in dtExclusion based on ID column (assumed that in both of these datatables ID is the common column to be matched)

Note : If by chance you get “ AsEnumerable is not a member of datatable ” issue then this link will help you to fix: AsEnumerable is not a member of ‘System.Data.Datatable’ - #2 by loginerror

Thanks
Vishal

[kkiw (Henry Wang)]

Hi,

I really liked this idea as it seemed simple. However, it got me another error:

“Add Data Row: This row already belongs to another table.”

Thanks for your suggestion though, it is appreciated!

[kkiw (Henry Wang)]

I tried this out, but can’t unfortunately get it to work. When I did a inner join, the output is a datatable where the results matched (7 rows out of 91). What I would like instead is the inverted of this: meaning I would like to have the other 84 rows instead.

Edit: It seems like I have solved the issue. Thank you for your input, and I hope you have a great weekend!

[kkiw (Henry Wang)]

This seems to have done the job. Thank you very much Vishal! I hope you have a great weekend