How can I filter a csv with a specific date format string for rows where the date is newer then a given date X?

Help Activities
, ,
1

Hi guys,

complicated title I know :smiley:

So here is what I got:

  • I have a CSV file with a few columns.
  • One of them is “Modified Date” and it has this format “dd.MM.yyyy HH:mm” ==> for example 01.01.2010 23:00"
  • This date I change from string to date and give it the format like this: dd.MM.yyyy
  • For now I have a complicated loop where I transform each item into date and then compare it with another date (not from this file, so the comparedTo date is static) ==> e.g. 01.01.2020 > 02.01.2020
  • This is very expensive and takes veeeeery long (the csv has over 5000 lines).

Now I wanted to basically ask if theres a better method for this with a simple filter or something. I found that there is a filter activity but that doesn’t give me the possibility to change the format of the current row’s date string, since my comparedTo date is in this format dd.MM.yyyy / where as the currentRow date has this format dd.MM.yyyy mm.HH(or at least I haven’t figured it out).

How can I make this much more performant without going the UI route (UI would be simple since I just open Excel and set a date filter on this column). But I would like to avoid going the UI Route if that makes sense.

Thank you very much guys.

2

Hi @GeraltDieSocke

Did you try LINQ query?
Here some interesting videos about LINQ, if you are not familiar with LINQ queries:
LINQ Training: UiPath | LINQ Training | Learn LINQ for UiPath from A to Z | LINQ Query Structure | LINQ Query Parts - YouTube

LINQ Group Function: UiPath | LINQ Group Function | Get Unique Rows from DataTable | Duplicate Rows | Not Duplicate Rows - YouTube

LINQ Let Function: UiPath | LINQ Let Function | Calculate variable in LINQ | Calculate new column with LINQ | Let Array - YouTube

LINQ IF Condition: UiPath | LINQ IF Condition | IF for Arrays | IF for Data Tables | IF for empty output of LINQ query - YouTube

LINQ Join Function: UiPath | LINQ Join Function | Inner Join | Left Outer Join | Right Outer Join | Full Outer Join - YouTube

2 Likes
3

Your youtube channel ? :slight_smile:
Glad that there are guys like you that do this :slight_smile:
I will check it out thank you.

So it should be possible with Linq right?
Thx

4

Yes it is possible and very performant. Just a few seconds to filter the table with 5000 rows

5

Holy moly sounds promising. I’m definetily hyped. Will get back to you with my solution :slight_smile:

6

Hi,

After reading you CSV data into a data table using Read CSV activity, for example- dtTest, You can assign the below LINQ query to a new data table for example -dtFilteredTable

(From d In dtTest.AsEnumerable Where dateTime.ParseExact(d(0).toString,“dd.MM.yyyy HH.mm”, System.Globalization.CultureInfo.InvariantCulture).Date < DateTime.Parse(strDateToCompare) Select d).CopyToDataTable

Here, strDateToCompare is the static date variable stored as a text (you can directly pass the date variable as well if you already have in a proper date variable) and the d(0) indicates the 1st column where my dates are coming in CSV. You can change the index value as per your requirement.

NOTE: Also, ensure in order to use LINQ queries the assembly reference: System.Data.DataSetExtensions must be added to your xaml file.

2 Likes
7

Hiho,

this is so fast wow. But unfortanetely the new datatable it builds has the same row length as the one it copies it. So the boolean check doesn’t work. It still has 5000 entries but it should have just a few when the date is set to for example: 18.10.2021 (comparedTo)

I will look more into it. Thx so much