I've been looking for ways to filter multiple values in a datatable but I'm only led to one solution, the Select() method. For those who were sold on UIPath being a drag-and-drop-no-coding-required solution to automation, this could be intimidating. Heck, if I didn't know about SQL I'd be dead beat right now. Yup! The only way (at least for now) to select multiple values from a column in a datatable is by using the DataTable.Select method.
Thankfully, it's not that hard.
Say I have this datatable called rulesDT,
Thankfully, it's not that hard.
Say I have this datatable called rulesDT,
And I want to get all rows where Home Location is either London, Manila, or Tokyo
If only UIPath had a feature like this in its Filter DataTable activity, it would be easy right?
UIPath wishlist: Allow IN operation
But since there's none at the moment, here's what worked for me.
Using DataTable.Select()
var rows = new DataRow[]; //Array of DataRow
var outputDT = new DataTable;
assign rows = rulesDT.Select("[Home Location] IN ('London','Manila','Tokyo')");
//Check if there are results
if(rows.Count > 1){
outputDT = rows.CopyToDataTable();
} else {
//Do something
}
The pseudo code above, when applied as a UIPath workflow, will return a datatable with rows that satisfy the query, in this case 5 rows.
It's similar to running a filter rule on MS Excel.
The reason we need to assign the results to a DataRow[] first instead of directly putting the output into DataTable is to avoid running into this exception in case there 0 rows found.
You cannot copy 0 rows to a DataTable, that's why we need to check first if the Select query returned at least one result.
Example for querying multiple columns
I want to get Partners between the ages 30 to 40 year old
rulesDT.Select("[Occupation] = 'Partner' AND [Age] >= 30 AND Age <= 40")
There is no BETWEEN operator so we have to use two comparators.
Querying dynamic values
If you want to select multiple dynamic values from a column, you need to build the parameters first then include it as a variable.
• Using a list of integer
var rows = new DataRow[]
var dynamicIntArr = {20,30,40}
var join_dynamicIntArr = String.Join(",",dynamicIntArr) //Result: 20,30,40
rows = rulesDT.Select("[Age] IN (" + str_dynamicIntArr + ")")
• Using a list of string
This requires more manipulation as you have to wrap each value inside single quotes. It would be best to prepare the string with single quotes beforehand for easier handling.
var rows = new DataRow[]
var dynamicStrArr = {"'One'","'Two'","'Three'"}
var join_dynamicStrArr = String.Join(",",dynamicStrArr) //Result: 'One','Two','Three'
rulesDT.Select("[Age] IN (" + join_dynamicStrArr + ")")
Very helpful links
- Where you can learn about DataTable.Select() .net method
https://docs.microsoft.com/en-us/dotnet/api/system.data.datatable.select?view=netframework-4.8 - Where you can learn about the filter expression syntax in C#
https://www.csharp-examples.net/dataview-rowfilter
