Filter Based on Date

Help
1

I have an excel CSV File with certain columns,

User Enrollment Date (mm/dd/yyyy) | Name | Phone Number
08/16/2018 Test 1 555
09/06/2018 Test 2 555
10/07/2019 Test 3 555
10/16/2019 Test 4 555

How can I filter the entire data table and remove rows based off the enrollment Date being greater or equal to Sept 04, 2018 (09/04/2018) and less than or equal to the current day - 7, in this case (10/07/2019).

In this example I would want to remove Row 1 and Row 4. I have tried using the filter datatable activity but am struggling to do so.

2 Likes
2

When your csv becomes a DataTable, did you confirm that User Enrollment Date really is a column of DateTime type?

3

Hi @mkkajtez

Use Datatable.Select(“[Enrollment Date]>=‘09/04/2018’ and [Enrollment Date]<=Now.AddDays(-7).ToString(“mm/dd/yyyy”)”.CopyToDatatable()
use remove data row activity and pass the value

Thanks
Ashwin S

4

I am getting a compiler error with the above statement

5

can you please share the screenshot

Thanks
Ashwin S

6

Try this:
Datatable.Select(“[Enrollment Date]>=#09/04/2018# and [Enrollment Date]<=#”+Now.AddDays(-7).ToString(“mm/dd/yyyy”)+“#”).CopyToDatatable

7

Thank you. Is there any way to specify date format on 09/04/2018? I think it is treating it as dd/mm/yyyy instead of mm/dd/yyyy @kadiravan_kalidoss

8

You can change the cultureinfo using an assign activity before reading the CSV.

Assign System.Globalization.CultureInfo.CurrentCulture = new CultureInfo("es-ES")

That is an example of changing it to Spanish (Spain) cultureinfo which. Choose your specific cultureinfo from here: List of culture name and code System.Globalization | C# Online Compiler | .NET Fiddle

Otherwise, if it is converting only some of the dates incorrectly and the cultureinfo doesn’t fix it, you will have to edit the CSV before reading as a datatable. Read it as text, change the date to a more specific format such (instead of 22-12-2019 change it to December 22 2019 as an example), then read as datatable. If it brings it in as a string, then use Datetime.ParseExact to change the string into a datetime.

9

never manually format datetime fields as string unless you really need it, this will save you thousands of hours in development problems :wink:

10

I am trying to use the filter datatable activity again. I have

Enrollment Date >= Date.ParseExact(“09/04/2018”,“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture)

I am still getting unexpected results. Is this how I would get dates to be treated as mm/dd/yyyy?

11

That is how you convert the string “09/04/2018” into a datetime variable, yes. However, you want to ensure that the CSV file is being read by uipath as datetime variables, and that the datetime coming in is in the correct format.

To check if it is coming in as a datetime, read the CSV to a datatable variable, then in a write line activity put: YourDataTable.Columns("YourColumnName").DataType.ToString

This will tell you if it is reading it as system.datetime or system.string

If it is string, then you will need to create a new column as type datetime, loop through each row and use datetime.parseexact to assign the newly created column as a datetime (parsed from the old date column). Then delete the column and rename your column to the old ColumnName if you choose.

Now you have a datetime column and you can prepare the filter as specified above. Be sure to include the number sign (#) before the date in your Select statement which signifies that you are comparing a datetime value.

If the datetime is getting converted in the wrong format, try the actions that I mentioned in my above comment to alter the cultureinfo before reading the CSV, or if that doesn’t work then alter the CSV directly before reading it in as a datatable.

1 Like
12

@Dave It’s actually showing up as System.Object

13

I would probably convert the entire column to datetime if it were me since leaving it as object is causing issues.