Filter DataTable(from Excel) on column containing Date

Help Studio
, ,
1

I am reading an excel to DataTable. I have a column “Date” of type string. I want to apply a filter(=,>,<) on it using a datetime variable. But my column “Date” is of string type (as Excel is coming from SAP and I am reading using read range).
I have read forum for more than 2 hours and tried multiple methods suggested by experts on UiPath forum and StackOverflow
including
DT.Select,
ExcelSAPDT.AsEnumerable().Where(Function…CopyToDataTable()
But not able to apply it. Any help will be appreciated.

2

Hi @SajidM

Check out this LINQ expression

ExtractDataTable = ExcelSAPDT.AsEnumerable.Where(Function(r) DateTime.Parse(r("Column Name").ToString)=New DateTime(2021,12,1) AndAlso DateTime.Parse(r("Column name").ToString)<New DateTime(2021,12,10) AndAlso DateTime.Parse(r("Column name").ToString)>New DateTime(2021,12,10)).CopyToDataTable

Regards
Gokul

3

When I use your expression, It gives error upon running,

image
image1074×383 78.8 KB

4

However I want to use a DateTime type variable, It errors out “disallows implicit converion from string to Date”

image
image1089×316 83.5 KB

5

Can you share the sample excel file and filter condition with us @SajidM

6

File.xlsx (8.7 KB)
I am not able to copy original file. however I replicated same scenario here in test file.
Date column has the same format as in original file.
I have to use datetime variable as my requirement is to apply filter for first of next month and first of previous month

7

Main.xaml (7.6 KB)

Hi @SajidM , check out this file that I worked based on your excel file.

Best regards,

8

Add Data Column with datetime datatype.

For Each Row in Datatable

  • Assign CurrentRow(“DateColumnName”) = DateTime.Parse(CurrentRow(“StringColumnName”).ToString)

This assumes your string dates are standard MM/dd/yyyy format. If not then you might need to use ParseExact instead of Parse.

You can remove the column with the date as string, if you want.

9

:ambulance: :sos: [FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues - News / Tutorials - UiPath Community Forum