How can I remove specific lines with dates over 30 days past due, a .txt file in UiPath?

Question

I want to remove all lines with current datetime-30 days, in my .txt file.

"2023-01-01 10:41:32";"AAAAAAA";"BBBBBB";"CCCCCC";"DDDDDDD";"XXXXXX";"FFFFFFF";"EEEEEEE" "2023-02-01 12:40:02";"AAAAAAA";"BBBBBB";"CCCCCC";"DDDDDDD";"XXXXXX";"FFFFFFF";"EEEEEEE" "2022-01-03 09:11:02";"AAAAAAA";"BBBBBB";"CCCCCC";"DDDDDDD";"XXXXXX";"FFFFFFF";"EEEEEEE" "2022-11-01 02:11:05";"AAAAAAA";"BBBBBB";"CCCCCC";"DDDDDDD";"XXXXXX";"FFFFFFF";"EEEEEEE" "2023-02-01 05:55:32";"AAAAAAA";"BBBBBB";"CCCCCC";"DDDDDDD";"XXXXXX";"FFFFFFF";"EEEEEEE" "2023-02-05 12:41:21";"AAAAAAA";"BBBBBB";"CCCCCC";"DDDDDDD";"XXXXXX";"FFFFFFF";"EEEEEEE" "2022-12-18 18:00:00";"AAAAAAA";"BBBBBB";"CCCCCC";"DDDDDDD";"XXXXXX";"FFFFFFF";"EEEEEEE" "2022-01-29 11:22:00";"AAAAAAA";"BBBBBB";"CCCCCC";"DDDDDDD";"XXXXXX";"FFFFFFF";"EEEEEEE" "2022-01-31 12:00:00";"AAAAAAA";"BBBBBB";"CCCCCC";"DDDDDDD";"XXXXXX";"FFFFFFF";"EEEEEEE"

when I save it I should only have those lines left:

"2023-02-01 12:40:02";"AAAAAAA";"BBBBBB";"CCCCCC";"DDDDDDD";"XXXXXX";"FFFFFFF";"EEEEEEE" "2023-02-01 05:55:32";"AAAAAAA";"BBBBBB";"CCCCCC";"DDDDDDD";"XXXXXX";"FFFFFFF";"EEEEEEE" "2023-02-05 12:41:21";"AAAAAAA";"BBBBBB";"CCCCCC";"DDDDDDD";"XXXXXX";"FFFFFFF";"EEEEEEE"

First I tried to copy to a CSV file, and it works fine. But I'm stuck when I want to filter the dates. Any help?

Read the date value, then ```Date.Parse(dateString)```. Finally look here for how to check day difference https://stackoverflow.com/questions/20635037/get-date-difference-in-vb-net — kwoxer, Feb 16 '23 at 21:10

score 0 · Answer 1 · answered Mar 29 '23 at 17:55

First read the CSV into a DataTable using the Read CSV activity.

After that, filter the table using LINQ and an assign activity:

The right side of that assign is:

(From r In table.Select
Where System.TimeSpan.Compare(
    DateTime.ParseExact("2023-02-01","yyyy-MM-dd",System.Globalization.CultureInfo.InvariantCulture).Subtract(DateTime.ParseExact(r("Column1").ToString(),"yyyy-MM-dd HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture)),
    System.TimeSpan.Parse("30")) < 0
Select r).CopyToDataTable()

(or, if your project is in C# instead of VB:)

(from r in table.Select()
where System.TimeSpan.Compare(
    DateTime.ParseExact("2023-02-01","yyyy-MM-dd",System.Globalization.CultureInfo.InvariantCulture)
        .Subtract(DateTime.ParseExact(r["Column1"].ToString(),"yyyy-MM-dd HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture)),
    System.TimeSpan.Parse("30")) < 0
select r).CopyToDataTable()

In those pieces of code, the date is hardcoded. To do it from the current date instead, use DateTime.Now instead of DateTime.ParseExact("2023-02-01","yyyy-MM-dd",System.Globalization.CultureInfo.InvariantCulture)

You could also conceivably do this with activities by making a new column with the Add Data Column, using a For Row in Data Table activity to compare dates and store it in the new column, a Filter Data Table activity to throw out rows where the date isn't new enough, and a Remove Data Column activity to remove the column you made.

Then use the Write CSV activity to write it back to the original text file.

How can I remove specific lines with dates over 30 days past due, a .txt file in UiPath?

1 Answers1