How to filter data table with variable?

Help
1

Hello UiPath society,

I have problem with filtering the excel file with variables and have no idea how to resolve it.

The algorithm looks as following:

  1. I am getting text from website from specific field, which contains 5 digits ex. 99999, the output is called Segment1.
  2. Then I am assigning to new variable Segment1Str = Segment1.ToString
  3. I am doing similar situation with second specific field, and after getting text I am assigning new variable Segment2Str =Segment2.ToString

For this moment, Segment1Str and Segment2Str represents two parameters (strings) which potentially I want to apply as a filter in sequentially first and second column in opened excel file.

The next step is opening the Excel Application Scope and indicating the excel file and creating DataTableDT as output, basing on overall file. And here comes the thing - I need to find fields in DataTable, filtering with 2 parameters: filter on Segment1Str on A column and paralelly Segment2Str on B column. There will be only one record that meets the two obtained earlier paramters

My first question - how apply this filter in Excel filewith variable? Moreover I do not know how to apply two filters in one expression.

I have prepared this code, but it does not work:
DataTableDT.Select(“1”=Segment1Str).CopyToDataTable()
and then do that again:
DataTableDT.Select(“2”=Segment2Str).CopyToDataTable()

To be honest I am stucked. I will be gratefull if you could indicate me what I should do.
Thank you in advance!

2

Hi

Why not use a LINQ expression?
They are more potent and user friendly than the Select method of the DataTable object

Something like this used in a single assign action, should return the rows as an array:

(From row As DataRow In DataTableDT.AsEnumerable() Where (row(1).ToString = Segment1Str and row(2).ToString = Segment2Str) Select x = row ).ToArray

After this, all you have to do is add the new rows to a new DT.

1 Like
3

Hello @radu_bucur, thank you for your quick response!

To be honest I am totally not familiar with C# expressions (so far!).
Do you mean I should use the assign action:

NewDataTableDT=(From row As DataRow In DataTableDT.AsEnumerable() Where (row(1).ToString = Segment1Str and row(2).ToString = Segment2Str) Select x = row).ToArray

Then I need to read the value from 3 column, 1 row (after applying the mentioned aboved filter, there will be only one row) and compare it with earlier obtained data. How I can read the indicated field in array?

Thank you again in advance!

4

Please try this

5

Hello @sara_s, thank you for your video. I have followed your instructions but I still receive an exception that: "Filter expression ‘99999’ does not evaluate to a Boolean term’. I am not using any Boolean variable and do not know where is the issue…

6

Hey it should be like this:

Datatable Filtered_Results = dt.Select(“[Column1]='”+param_1+“’ AND [Column2]='”+param_2+“'”).CopyToDataTable()

For your assistance, find the attached sample: Datatable select more then one filter sample.xaml (9.2 KB)

Note:- condition you can use like AND, OR as per your needs :slight_smile:

Regards…!!
Aksh

5 Likes
7

Please send me your data table structure with sample data . And the filter condition which u applied

Thanks

8

@Sara_s thank you, finally I resolved the problem and the file is filtered. I wrongly used assign option, cause I missed the apostrophes inside quotation marks. Thank you again!

9

use like this example : “dept_id=3 and last_name=‘Smith’”

image
image1066×550 18.5 KB

10

Of I thought the issue is solved but… for moment it is still complicated :smiley: There was no problem with working when the values were declared with static value. The problem appears when it is connected with variable.

So… I declared:

Segment1= “Segment1='”+Segment1a+“‘’”

The Segment1a is a variable, obtained earlier.
The Segment1 is also a name of a 1st column in excel.

When I try to run this, I notic message: "The expression contains an invalid string constant ‘99999’'.

Ehh I am probably too dumb!

12

That was exactly what I am trying to do. The thing is that I need to set the filter in variable. So in this case “dept_id=STRING1 and last_name=STRING2”. If I post there string names it does not work.

15

Linq is useful if you want to run multiple queries against a DataTable, as it will be less expensive. while making a single query go with data Table function ‘select’. Following is the linq query.

dataTable = ((From row As DataRow In datatableName.AsEnumerable() Where (row(0).ToString = "xyz" AND row(1).ToString="abc" ) Select x = row ).ToArray).CopyToDatatable()
1 Like