How to Filter Data Table

Thank you @ClaytonM, it still helps me now :slight_smile:

Hey @ClaytonM ,I want to delete few rows on the basis Variable value match and then get the out in the same name of excel, please suggest, PFB XMAL with sample

Delete Processed Policy.zip (24.4 KB)

Hi,

Not getting , How to filter the data in ui path??
can anyone explain???

Hi,

Apologies, as I don’t have time with year end to provide you with .xmal samples.

Your Delete process will look like this (depending on your requirements:

Assign arrayRows = dt1.AsEnumerable.Where(Function(row) row("column1").ToString.Trim = value1 and row("column2").ToString.Trim = value2).ToArray

ForEach row In arrayRows       'with Type Argument as DataRow
     Delete Row activity       'with row in the parameters to delete

So in summary, filter datatable to an array of rows that meet a condition, then run them through a loop to delete all rows from the array in the datatable.

Hope that helps.

Regards.

1 Like

Thank you for response
Wish you hpy nyr

hi @ClaytonM I did a solution just as previous once , not able to clear below error, attaching my solution please check if you can.

Soltuion.zip (10.7 KB)

image

@hkjobs1988 Hi there,

You have a syntax error in your Assign.

Change the “is” to “=” and also you will need .ToString
I adjusted your expression to this:

  DT_SFDC_Ext.AsEnumerable().Where(Function(row) row("Document Status").ToString.ToUpper.Trim = "OK").CopyToDataTable()

Regards.

C

could you please help me if my string has got this value ====> A - B
instead of just an A

Hi @shrganes

Are you wanting to filter your table by the value “A - B”? If not, could you provide further details?

We can consider all possibilities of A - B, A-B, A- B, A -B, et cetera. Therefore, I would suggest using a pattern or multiple criteria using .Contains.

Here is a quick solution using Regex to filter your table down to all dataRows with value “A-B”:

arrayRows = dTable.Where(Function(row) System.Text.RegularExpressions.Regex.IsMatch(row("Group").ToString,"A(.*)-(.*)B")).ToArray()

That will return an Array of dataRows that you can process or update the original table with.
For example this psuedocode will update a column for each row in the original table using the rows,

ForEach row In arrayRows   with type DataRow
Assign row("column") = "newvalue"

Regards.

1 Like

The value in the column is for eg… Bread - butter

I want to filter all rows with this name

Cool, you can replace “A(.)-(.)B” with something like the below
“Bread(.)-(.)butter”
or to ignore case:
“[bB][rR][eE][aA][dD](.)-(.)[bB][uU][tT][tT][eE][rR]”

Thank you…

Just wanted to ask. The Filter Data Table is not available in my activities. But the UiPath.Core.Activities package is installed properly. The available activities for me only under the data table is Build, Clear, Generate, Merge and Output.

Thank you for answering.

Hey @ClaytonM

I applied below syntax for filter data table , But getting error , Please let me know any another alternate please

Image5

Hi.

where you have vMainDT.Select(), change that to vMainDT.AsEnumerable
Although, I’m not sure it will make a difference.

If the column does not exist, then make sure that vMainDT has the column (you can check it with Output Data Table activity then show the string in a Message Box or Write Line). Keep in mind, if the file does not exist, the Excel Scope will create an empty Excel file with no column names.

Other than that, your syntax looks good.

You can try using lambda expression, which is basically the same thing.
That would look like this:

vMainDT.AsEnumerable.Where(Function(row) row("Division").ToString.Trim.Equals("07") ).CopyToDataTable

Regards.

Got it,

In read range activity I for to check the Headers option

1 Like

Hello All, I’m trying to filter an excel sheet with three columns and then Im writing to another excel sheet.
But filtered data is not getting written into new excel sheet. I’m attaching .XAML file, please suggest.ExcelPractice2.xaml (16.6 KB)

Hi @nikhil_rao,

In write range click add headers & try once
no need of using for each row,directly you can give filter data table output in write range

Some minor edits:
dt2 = (From row In dt1.AsEnumerable() Where row("ColumnName").ToString.Equals("") Select row).CopyToDataTable()

Hi Dear,
You can use Activity Filter Data Table

image

Thanks!

2 Likes