Filter Datatable By All Items In Collection List

Hi there,

Would anyone be able to advise on how I may be able to filter my datatable, using all items that are in a Collection List I have created?

I have an automation which iterates through a drop-down list on a webpage (which can vary in length/names of items may be different each time) and uses the Get Attribute activity to add those items to a list. I then want to filter a datatable that I have assigned in another part of my process, and then filter that datatable to only show rows where the Collection List items appear in a particular column.

Thanks in advance!
Sam

Hi @sam.willson,

For comparing rows with data table you can use LINQ query with a lambda expression as
First, use a For Each Loop and iterate it with your collection
Assuming the iterating index is i

dt_Name.AsEnumerable().Where(Function(x) x("ColumnName_WhichYouHaveToCompare").ToString().Contains(list_yourList(i).Trim())).ToList()

This will return the List of all matched values.

You can share me the data table value and collection value if possible in excel or CSV
I will modify it accordingly

Regards,
Firoz

HI @sam.willson

Try this expression

(From d In YourDT.AsEnumerable()
Let f= String.Join(",","YourCollectionList")
Where f.Contains(d("Yourcolumn Name").ToString)
Select d).CopyToDataTable

Hope this helps!

Regards
Sudharsan

This may give unexpected results because a value may match part of the joined string while not actually matching one of the values. For example, if the joined list is (value1,value2,value3) then if Yourcoumn Name has a value of “val” it’ll match even though it shouldn’t.

For these kind of matching operations, you should always surround with another character.

(From d In YourDT.AsEnumerable()
Let f= “[” + String.Join(“],[”,“YourCollectionList”) + “]”
Where f.Contains(“[” + d(“Yourcolumn Name”).ToString + “]”)
Select d).CopyToDataTable

Now your joined list would be [value1],[value2],[value3] and a Yourcolumn Name of [val] will not match.

1 Like

Yes @postwick

Thanks for the input , got something new

Regards
Sudharsan

We assume
YourFilterCollection | A list of Strings - the values from the dropdown, all are trimmed

(From d In YourDataTableVar.AsEnumerable()
Let v= d("YourColName").toString.Trim
Where YourFilterCollection.Contains(v)
Select r=d).CopyToDataTable

Handling empty results:
:ambulance: :sos: [FirstAid] Handling of The source contains no DataRows exception - News / Tutorials - UiPath Community Forum

When using the Contains method from a collection we do not check for part matching, as it has to match the item

grafik

we can avoid the same string construction for each loop

Hi @ppr, this has worked perfectly, exactly what I was looking for - thank you! I’ve also put my assign into a try catch, so if it fails/can’t find any results, I’ve programmed my process to log the information elsewhere. Thanks again for your help!

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