How to Filter Data Table

Help
, ,
1

Hi,

I have excel spreadsheet and used Read Range to store results to Data Table.

Sample:
Student | Group
Allan | A
Anna | B
Charlie | C
Chris | C

Here I would like to filter all rows whose Group = ‘A’

My current approach is I used “For Each Row” → “Get Row Item” → “If” to check if Group = ‘A’

Is there a more efficient way to do this?

Thanks

2 Likes
2

Hello,

You will probably like the .Select function for filtering down datatables to an array of datarows, which you can then use a For Each on.

Example:
array[of type datarow] = dt.Select(“[Group] = A”)

You can also use ‘and’ and ‘or’ as well as concatenate variables to the string.

Example:
array[of type datarow] = dt.Select(“[Group] = “+variable+” and [Group] =”+variable)

Thanks!

3 Likes
3

dear Uipaht_test LOOL

yes you need to do this :slight_smile:

on the invoke method make sure you add the parameter

image.png702×207 20.2 KB

the final step will be to append the dataTable2 to a excel file.

1 Like
4

Hi All,

Trying to filter the data table and get the filtered output in excel , filtered file is coming blank

below is my file , request suggestions

FOR TPA.xaml (14.4 KB)

5

Your workflow looks good to me, but was wondering if you adjusted your rowSelected assignment:
ExtractDataTable.Select(“[Tranno]=”+trans_number)
so with brackets

Alternative filter assignment can be like this:
ExctractDataTable.AsEnumerable().Where(Function(row) row(“Tranno”).ToString=trans_number).ToArray()

If that doesn’t help, you could provide an example Excel file so we can use it with your .xaml file to check it.

Regards.

6

hey @ClaytonM Thank you for response , attaching the scrubbed result from the targeted page , just changing the customer details for data security TPA Policies extraction.xlsx (26.4 KB)
:slight_smile:

7

Hey,
So I found the problem was with the ImportRow method. I’m not exactly sure why that wasn’t working.

However, you don’t really need to do that unless you have an existing table you are adding to.
So what I did was change the Assign activity to filter the rows directly to the datatable skipping over the Invoke Method. Below are screenshot and workflow.

image
image904×84 8.18 KB

93428c340311b3e34de3f661b6498eee59e8999f.xaml (18.6 KB)

You’ll need to change the Data Scrape and Excel Scope back to how you had it.

EDIT: I’ll also add that if you would like to process each line of the filtered data, then you can take the new dataTable variable and run that through the ForEach loop. :slight_smile:

Regards.

5 Likes
8

Many Thanks @ClaytonM worked out well

Please share any link where i can read more about the function you have used in above screenshot.

Regards
Hitesh

9

You’re welcome!

If you do a search on how to filter datatables in vb.net or if you know the function “where vb.net”, you can find various ways to use this. You can also find c# syntax which is the same thing as vb.net but just change the => part to Function(). You can also look at LINQ or lambda expressions in vb.net.

Alternative example of the Where using LINQ is
(From row In datatable.AsEnumerable() Where row(0).ToString Equals “” Select row).CopyToDataTable()

I don’t have a really good link that explains all the different methods to manipulate datatables, but hope my tips help get the info and examples you need.

EDIT: You can use the same things with Arrays too minus the CopyToDataTable

Regards.

2 Likes
10

Thanks @ClaytonM

11

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

12

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)

13

Hi,

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

14

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
15

Thank you for response
Wish you hpy nyr

16

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

17

@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

18

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

19

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
20

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

I want to filter all rows with this name