Filter datatable from csv on date >= Today

[Luke69 (Luke)]

I have a datatable build from a .csv file containing dates in a column (header:Date); format “18-05-2024”. I want to filter these dates greater then today
Using a ‘for each - if’ statement Cdate(CurrentRow(“Date”).ToString)>Today works just fine but is very much not efficiënt
I want to use a filter Data Table but then run into the problem that “Date” >Today is not filtering as probably the value cannot be recognised as date.
how can I automate without first trying to convert the column values into date type in an extra column?
Probably something very simple, but yes, I am an exited newby

[mkankatala (Mahesh Kankatala)]

Hi @Luke69

Use the below LinQ Expression to get the required output,

Process -
→ Use the read range workbook activity to read the excel and store in a datatable variable called Input_dt.
→ Use assign activity to write the LinQ Expression,

- Assign -> Output_dt = (From row In Input_dt
                         Where (DateTime.ParseExact(row("Date").ToString(), "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture))>=DateTime.Now
                         Select row
                          	).Copytodatatable()

→ Use the Write range workbook activity to write the Output_dt to Excel.

Check the below workflow for better understanding,

Input -

Output -

Hope it helps!!

[Anil_G (Anil Gorthi)]

@Luke69

Welcome to the community

You can use linq expression liek this in assign

Dt = dt.AsEnumerable.Where(function(x) Cdate(x("Date").ToString)>Today).CopyToDatatable

Now your table will have only the filtered rows

Cheers

[Luke69 (Luke)]

@Anil_G

.AsEnumerable.Where(function(x) Cdate(x(“Date”).ToString)>Today).CopyToDatatable

Worked like a charm, thanks. made my day

[Luke69 (Luke)]

the globalisation I had already done, therefore the if statement worked. So the solution was in the assign which you now incorporated as well. THanks

[Anil_G (Anil Gorthi)]

@Luke69

Glad it helped.Happy Automation

Please close if resolved

Forum FAQ - How to mark a post as a solution

Forum FAQ - How to mark a post as a solution This document is part of our beginners guide. This article will teach you how to properly mark a post as a solution. We were closely following our last UiPath Forum feedback round! topic and carefully extracted all the bits of feedback that was provided. As such, we would like to tackle the topic of solutions on our Forum and how to properly use them. When is the topic resolved? The topic can be considered resolved when the topic author has fo…

Cheers

[mkankatala (Mahesh Kankatala)]

If you don’t made the globalization for this, my solution will for you. If the data in the Excel in General or in text format the CDate will not work in that case we have to use the globalization based on the pattern of the Date.

If the Input is in MM/dd/yyyy format the CDate will work If there is a specific date format as you mentioned in the question 18-05-2024 which is in dd-MM-yyyy will not work, so in these cases we have to use globalization and make into the required date format.

Hope you understand!! @Luke69