Filter table on Excel

Help
1

Hi,
I have this requirement:

  1. Need to filter for all rows where status <> red
  2. The last login date < today’s date
    I need to use this data for further processing. Which is the best way?
    If you have any sample workflow please could you share?

Thanks,
V

2

Forgot to attach the sample excel file. How do I do it? But below is the sample:

First Name Last Name Status Last Login Date Email
abc wxy Red Jan-31-2019 abc@test.com
abc xyz Green Jun-30-2019 abc@test.com
abc sdf Yellow Jul-18-2019 abc#test.com
abc wxy Red Jul-18-2019 abc@test.com
  1. List item
3

@Vidya_Lingappa

As it is structured data use Data scraping Activity to read the data and will give you output as dataTable and say ‘DT’. And then try below expression to filter it.

         filteredDT = DT.Select("Status <> 'Red' AND [Last Login Date] < 'Now.Tostring("MMM-dd-yyyy")'").CopyToDataTable

And other way is to use Filter dataTable Activity to do this.

https://activities.uipath.com/docs/filter-data-table

1 Like
4

Hi @lakshman,
Thanks for your clarification. Do you have a sample workflow you can share with Filter Data activity particularly with date filters?
V

1 Like
5

Hi Lakshman,

I am getting runtime error. Filter table: The range does not exist. Please help.

Thanks,
V

6

@Vidya_Lingappa

Could you please show me screenshot of Filter dataTable Activity once and need to check once how you passed it.

7

Screenshot%20(71)
Screenshot%20(71)1920×1080 162 KB

Could you please tell how to filter data table in WIID>200000 AND DESCRIPTION=REASEACH CLIENT SECURITY CHECK AND TYPE=WI2??

Uipath version 19.7.0 in select method i got error…copy to datatable is not a member of system in array…

8

@suneel070

Welcome to the UIpath Community.

It’s looks like Status column showing values as 0 and it’s wrong. Could you please reset the ACME data and then try once.

You can use below expression to filter data.

filterDT = yourDT.Select(“WIID > ‘200000’ AND DESCRIPTION = ‘REASEARCH CLIENT SECURITY CHECK’ AND TYPE = ‘WI2’”).Toarray.CopyToDataTable

9

Filterdata, what kind of variable?after.ToArray it is not coming. Copy to datatable??

10

@suneel070

Here, filterDT and yourDT both are variable of type DataTable.

11

Hi,

I have a date column in my excel data table. I have to filter out based on dates less than today. How do I do it? Could someone help me with the logic.

Thanks,
V

12

Fine
we can mention like
yourdatatablename = yourdatatablename.Select("[youcolumnname] < ’ # Now.ToString(yourdateformat) # ’ ").CopyToDatatable()

where yourdateformat means the dateformat of the value from the date column in your excel
we can check that with a simple write line activity like
row(“yourdatecolumnname”).ToString
which will give us the value of date in output panel from where we would know the format and mention the same date format there in the above Now.ToString(yourdateformat)

Cheers @Vidya_Lingappa

13

edit :slight_smile:
yourdatatablename = yourdatatablename.Select("[youcolumnname] < # " + Now.ToString(yourdateformat) + " # ").CopyToDatatable()

in the above query

14

Hi @Palaniyappan,

I tried with the logic you provided above. My filter bot runs and it does not do anything. Do you have a sample workflow, so I can compare it?

Thanks,
Vidya

15

hope this could help you

it has similar syntax

kindly try this and let know for any queries or clarification

Cheers @Vidya_Lingappa

16

did that work buddy @Vidya_Lingappa

17

Hi @Palaniyappan ,
which example are you referring to? Sorry I did not understand. Please let me know.

18

Good luck getting the FilterDataTable to work