Filter date for multiple format in CSV

Help Activities
, , ,
1

I have a CSV that I read range and need filter out T+1 dates. The format in the excel is “MM/dd/YYYY”. However, when the date and month is single digit, for example today (2nd January 2024, in MM/dd/yyyy is 1/2/2024, it recognizes as 1st February 2024), therefore my filter does not work. what is the best way to filter T+1 data?

2

Hi @aqiffm

If possible can you share the CSV file with dummy data.

Regards

3

Hi @aqiffm

→ Use read CSV activity to read the csv file and store in a datatable variable, let call it as yourDatatable
→ Use for each row in datatable activity to iterate the each row in the datatable.
→ Inside for each insert the assign activity

- Assign -> originalDate As String = row("OriginalDateColumn").ToString()
- Assign -> parsedDate As DateTime = DateTime.ParseExact(originalDate, "M/d/yyyy", CultureInfo.InvariantCulture)
- Assign -> correctedDate As DateTime = parsedDate.AddDays(1)
- Assign -> Currentrow("CorrectedDateColumn") = correctedDate.ToString("MM/dd/yyyy")

→ Use the Filter DataTable activity to filter rows where the corrected date is equal to T+1.
→ Specify the condition in the Filter Wizard.
→ Now, you have a DataTable with only T+1 dates. Continue with your further processing.

Hope it helps!!

4

@aqiffm

First please check how the date is readinto datatable…for that after reading the file open the locals panel and then open the datatable and check the date how it is in the datatable and accordinglt convert the string to date format of required type

Generally the way you see on excel and the way you get in datatable might be little different

Cheers

5

using for each row is a really big and it consumes time, but i will test this

1 Like
6

You can convert to CSV and test (I cant upload .csv format here)

Sample Forum.xlsx (69.3 KB)

7

i think there’s some confusion here.

I have a list of date; I need to filter for T+1 dates. However, in the list of dates have multiple format, format as follows:

  • 2nd January 2024 (1/2/2024, is being read as 1st February 2024 - Which is wrong)
  • 24th December 2023 (12/24/2023, is being read as 24th December 2023)

Therefore, I want to find a way on how to cater both and filter out T+1 dates

8

Okay @aqiffm

Change the format of date for below assign activity.


- Assign -> Currentrow("CorrectedDateColumn") = correctedDate.ToString("dd/MM/yyyy")

Hope you understand!!