Filter wizard to select records in the data table less then current date minus 2

Help
, , , , ,
1

Hi All,

I am doing a POC to scrap Ratings and Reviews from Amazon.com. Reading the data in the data table is working fine. However I am using Filter wizard to remove any reviews older than current day minus 2 days using ‘Datetime.Now.AddDays(-2).ToString(“MM/dd/yyyy”)’. This part is not working.

The date on the website is in May 18, 2018 format. I am using filter wizard soon after reading the data from the website using ‘Data Scraping’ and before writing it to the excel.

Please advise.

Thanks,
Ashish

2

Here is the wizard setting:

Capture
Capture943×271 25.4 KB

3

Hi @agoyal1980

Dates in data table seems to be in string format. Convert them to date format (MM/dd/yyyy) first and then use the filter.

Hope this helps.

4

Is there a way to do this without first writing the data table to excel. The column name is ‘ReviewDate’ in the data table.

5

Once you scrap the data, it will be stored in datatable. So you need to convert string values in datatable to date type while applying the filter and write filtered datatable to an excellent file.

6

I am new to UIPATH, can you please share details on how to go about this step?

7

@agoyal1980
Give a try on doing IT with a linq Statement
Use an assign Activity
In right Side:
YourDataTableVar.dtDemo.AsEnumerable.Where(Function (row) Convert.ToDateTime(row(0).tostring)>= Datetime.Now.AddDays(-2)).CopyToDataTable
Left Side assign e.g dtFiltered - Datatype is datatable

8

image
image1360×137 15 KB

9

just give me a few mins i was writing statement on cell phone and do currently crosscheck on laptop

Ok find here a sample for demo purpose
agoyal1980.xaml (7.3 KB)

statement as above (both corrected)
YouDataTableVar.AsEnumerable.Where(Function (row) Convert.ToDateTime(row(YourColNameOrIndex).tostring)>= Datetime.Now.AddDays(-2)).CopyToDataTable

1 Like
10

@agoyal1980
In a next round once you got this working then I would suggest to change
Convert.ToDateTime to a stronger Conversion method that let you aloow to get control with a format string

DateTime.ParseExact() Method would be a recommendable option
Example: Datetime.ParseExact(YourDateTimeString,“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture)

System.Globalization.CultureInfo.InvariantCulture or your specific CultureInfo

1 Like
11

Does this result in complete datatable with updated date column included or just with the date column?

also does this remove the rows which do not match the criteria?

12

the result is filtering and keeps only the matching rows. If you assign to your origin datatable then its like removing the not matching rows. If you assign to another Datatable (I would suggest) then result is same (only the matching rows) but you have not lost the other rows. Does it answer your question?

13

I used your ExtractDataTable.AsEnumerable.Where(Function (row) Convert.ToDateTime(row(“ReviewDate”).tostring)<= Datetime.Now.AddDays(-2)).CopyToDataTable in assign and then wrote to excel. however the old records are still showing up.

14

@agoyal1980
Just choose one of the options

  • run and play with my standalone xaml e.g. with sample data
  • share your xaml so we can have a look on it
  • give a screenshot from debug on what is inside in your datatable
15

@agoyal1980
Maybe one of my last corrections were missed out. Use >= and have a check

16

Did we try with select method on this
Kindly have a view on this thread

Cheers @agoyal1980