Filter DT based on Date Time

[rjackson12 (RJackson)]

I have an Excel File with a column in this format “MM/dd/yyyy HH:mm” with column type General, I need to filter the file and only process the records created after yesterday’s date at 10:00 AM.

If my excel file column looks like this (General Type Column):

10/30/2021 10:29
11/1/2021 14:53
11/2/2021 9:29
11/1/2021 8:14

The bot will only need to process these rows based on the condition:

11/2/2021 9:29
11/1/2021 14:53

Any suggestions on how to accomplish this?

[Thomas_Mitchell]

Hey RJackson,

Read the whole excel file and filter in uipath using a linq query. It Would look like this…

dt_Input.asEnumerable().Where(Function(x) CDATE(x.item(“DateColumn”).tostring) >= CDATE(date.Now.Date.AddDays(-1).ToString(“MM/dd/yyyy”) + " 10:00:00").ToString).CopyToDatatable

[ppr (Peter Preuss)]

@rjackson12
As a filter result can also be empty, we should handle defensive the empty filter result. CopyTodDataTable would throw an exception in that case

Setting up the FilterDate

or dynamicly to every yasterday 10:00:
now.Date.AddDays(-1).AddHours(10)

then we can filter with a LINQ:

(From d In dtData.AsEnumerable
Where Not If(IsNothing(d(0)), True, String.IsNullOrEmpty(d(0).toString.Trim))
Where CDate(d(0).toString.Trim) >= FilterDate
Select d).toList

And handle defensive the empty filteresult incase it occurs:
dtFiltered = drFiltered.CopyToDataTable

Kindly note:
10/2/2021 9:29 is filtered out as it is before yesterdays 10:00

In case of variances, we can adopt also the date conversion function e.g. uising DateTime.ParseExact(… instead of CDate(

Find starter Help here:
DT_FilterDates_bySpecificDateAndTime.xaml (9.0 KB)

[Thomas_Mitchell]

Thanks PPR. Just to give the option…

As an alternative, a .Any method could be used in an if statement to prevent the exception.

[ppr (Peter Preuss)]

@Thomas_Mitchell
Just updated the post as 1 Bug was to fix

The Any-approach has the side effect that the work is to do twice (once for any, once for the filtering)

[rjackson12 (RJackson)]

I am currently trying this approach and run into the following issue:

SearchDate is now.Date.AddDays(-1).AddHours(10)
PRDTFiltered is my DT

Currently Failing on the filter within the assign activity.

Any Suggestions?

[ppr (Peter Preuss)]

in your data is a columnvalue “Shabreckae”. As this is not a valid datetime string it cannot be converted and is throwing an issue.

Have a check on your data. We could adopt the LINQ and filter out all invalid datetime strings. But it is more recommended to have a requirement analysis on:

• what data variances os to expected
• what can done on the side which is creating this data

[rjackson12 (RJackson)]

The column type in Excel is general as I mentioned in my original post.

I cannot make any changes on the site creating the data and there will be no variances, it will only be type general and looks like this “11/1/2021 14:53”

Could we find a workaround for this?

[ppr (Peter Preuss)]

@ppr

“Shabreckae”.

But you agree that this value “Shabreckae” is not a dateTime string, right?

Maybe you are refering to the wrong column. Just check the columnindex or use the Columnname
like d(0) vs d(YourColName)

[rjackson12 (RJackson)]

Using this: d(YourColName) worked!

Thank you!!