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):
The bot will only need to process these rows based on the condition:
Any suggestions on how to accomplish this?
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
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:
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
And handle defensive the empty filteresult incase it occurs:
dtFiltered = drFiltered.CopyToDataTable
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)
Thanks PPR. Just to give the option…
As an alternative, a .Any method could be used in an if statement to prevent the exception.
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)
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.
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
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?
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
Using this: d(YourColName) worked!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.