Error encounter when filtering past 6 months record from csv file

Hi,
I am trying to filter the data table based on the date to get the past 6 months’ record.
dt = dt.Select("[Requested On] >= ‘"+date_before.ToString +"’ AND [Requested On] <= ‘"+date_now.ToString+"’").CopyToDataTable().
and this throw me this error
image

I am still very new to uipath and can someone help me to get pass this error?

Hi,

First, check your variables (dt, date_before and date_now) have value you expected.

Next, check there is record you want to get in the dt, because CopyToDataTable method return exception if there is no datarow item.

Finally, can you try the following?
dt = dt.Select("[Requested On] >= #"+date_before.ToString(MM/dd/yyyy)+"# and [Requested On] <= #"+date_now.ToString(MM/dd/yyyy)+"#").CopyToDataTable

or

dt=dt.AsEnumerable.Where(function(r) DateTime.Parse(r.toString)>=date_before and DateTime.Parse(r.toString)<=date_now).CopyToDataTable

Regards,

1 Like

Hi, I am able to through the previous error and now I encounter another error
image
How is the operation cannot be perform?

Hi,
Can you share type of your variable?
Are date_before and date_now are string type?

Or probably the following will work as what you expect.

dt.AsEnumerable.Where(function(r) DateTime.Parse(r.toString)>=Now.AddMonths(-6) and DateTime.Parse(r.toString)<=Now).CopyToDataTable

Regards,

Hi Lau_Wei_Ting, possible to send your csv file? with the contents of the csv changed? I can try to test it out for you.

Alternatively, I would recommend

  1. use “Read CSV” to import the CSV file into a Data Table
  2. Use “Filter Data Table” and filter the Data Table above with the conditions you stated.

both date_before and date_now are on DateTime variable
This is my xaml:
testing.xaml
time.xlsx

In my xaml I been using read csv however, file upload selection does not support csv file type hence, I change my file into xlsx file type so that I can upload a sample file here.

I had tried using the Filter Data Table, however, I am not manage to do so.

Hi,

Thank you for sharing your workflow and data.

Can you try the following?
You might need to remove last blank row of your input csv file.

dt = input.AsEnumerable.Where(Function(r) datetime.ParseExact(r("Requested On").ToString.Trim,"d MMMM, yyyy h:mm:ss tt 'SGT'",nothing) >=date_before And datetime.ParseExact(r("Requested On").ToString.Trim,"d MMMM, yyyy h:mm:ss tt 'SGT'",nothing)<=date_now).CopyToDataTable

Regards,

Hi,
I am able to filter the result out. Thank you so much for your helping. :smile:

1 Like

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