Remove dates from Column that are equal or less than Current Date

Help Studio
, , ,
1

Hi All, I am attempting to do the Invoice extraction RPA Challenge. I have scraped the Data Table along with the URL and have also converted the string Date Column to a Date type. I want to remove the dates that are upcoming and keep only the dates that are present or in the past. I tried to do this with invoke method activity by assigning a variable with the expression ExtractDataTable.Select(“[DueDate] <= ‘+CurrentDate+’”) to use in invoke method activity with Method Name: Remove. When i run this nothing happens and get the exact Data Table without any filter as stated above. Would be great if anyone could help me out. TIA :slight_smile:

image
image1023×535 18.8 KB

2

Are the dates formatted the same way?

3

Yes, the dates are formatted the same way. I used this line of code in a for each row in the DataTable row(“DueDate”) = DateTime.ParseExact(row(“DueDate”).ToString,“dd-MM-yyyy”,System.Globalization.CultureInfo.CurrentUICulture.DateTimeFormat) and I have assigned CurrentDate = DateTime.Now.
Output of Data Table is below
image

4

make sure your times are formatted the same Datetime.Now has format as well. See image below
Datetime

1 Like
5

Thanks Matt, will check and confirm now.

6

@Anish_Kumar_S - you need to surround the datetime value with # in your select statement. The # signifies that it is a datetime

ExtractDataTable.Select("[DueDate] <= #"+CurrentDate+"#")

1 Like
7

Hi Matt thanks for pointing this out, I have fixed the formatting now. I used a wrong expression for conversion. I opted to use remove Data row as Invoke method didn’t work out for me. Here I am using if else condition for comparison and then if its true then those rows are added to a Data Row variable and after the loop I have used a Remove Data row activity to remove the future dates. This didnt work as well I still have the future dates. Have attached the xaml file. Literally losing my mind. Would really appreciate it if I get a solution.
Scrape Data.xaml (17.2 KB)

8

Hi Dave thanks for suggesting this, but i get a error when I use the expression that you have mentioned. So as an alternative I tried using Remove Data Row activity and that didnt filter out the future dates. Have attached the xaml file for you to have a look at. TIA :slight_smile: It would mean a lot if I get a solution for this.test.xlsx (7.7 KB) Scrape Data.xaml (17.2 KB)

9

@Anish_Kumar_S I don’t see the select statement anywhere? And what was the error with the select statement? It works just fine for me

EDIT: Just to confirm, the select statement returns an array of datarows. To delete those rows from your datatable you dont use invoke method, but instead use a for each statement (of type datarow) and supply the array of datarows you returned from your select statement. Then within the for each loop you can use the remove data row activity. If you are stuck with the removal portion, please do a search on the forums as there are many posts describing how to remove rows from a datatable using a for each loop :slight_smile:

1 Like
10

Thanks Dave I will have a look in the forum. btw The error that I got earlier was, "option strict on disallows implicit conversions from ‘date’ to ‘string’ ". Have attached a xaml file for testing just this case. Kindly clarify :slight_smile: TIASelectFilter.xaml (7.1 KB)

11

Okay try using the the filter datatable activity.
image
Click wizard
image

output of dtFilter:
product,date,year
phone,03/07/2020 13:30:00,2020

Note i did <= so if you needed >= you have to change it.

2 Likes
12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.