Filtering the datatable between Dates

[quick_123]

Hi,

I have a datatable of format
Sl.no, Date, Name
String,String,String

Here, I want to filter the date column between Dates,
In Filter Datatable activity, It is not filtering since The column Date is of format String, any leads on converting the Column “Date” from String to DateTime format

[ashokkarale]

@quick_123,

Try LINQ approach.

How to apply date filter using LINQ in UiPath

Problem statement : We have a datatable that contains the order details for all customers for past few years and we need to extract the data only for one specific year [SampleData] In the above datatable as you can see we have records from 2021 and 2022 and we need to extract the records for year 2021 only. Here is how you can do it Drag the assign activity to the workflow, write the following query [Query] Run the program and see the magic YouTube: How to apply date filter u…

Thanks,
Ashok

[quick_123]

Hi,

I tried this method
dtOutput=(From row In dtInput.AsEnumerable
Let dtOutput= DateTime.ParseExact(row.Item(“Column1”).ToString,“MM/dd/yy”,Globalization.CultureInfo.InvariantCulture)
Select row).CopyToDataTable

but I am getting this error
System exception.Expression Activity type ‘VisualBasicValue`1’ requires compilation in order to run. Please ensure that the workflow has been compiled. at Source: Assign

[vrdabberu (Varunraj Dabberu)]

Hi @quick_123

VisualBasicValue 1 error refers that your double quotes aren’t right. So, please retype the double quotes.

Try this:

dtOutput = (From row In dtInput.AsEnumerable
            Let parsedDate = DateTime.ParseExact(row.Item("Column1").ToString(), "MM/dd/yy", Globalization.CultureInfo.InvariantCulture)
            Select row).CopyToDataTable()

Regards

[quick_123]

the date is of format MM-dd-yy, so it is showing error is not valid DateTime

[vrdabberu (Varunraj Dabberu)]

Hi @quick_123

Try this:

dtOutput = (From row In dtInput.AsEnumerable
            Let parsedDate = DateTime.ParseExact(row.Item("Column1").ToString(), "MM-dd-yy", Globalization.CultureInfo.InvariantCulture)
            Select row).CopyToDataTable()

Regards

[quick_123]

this worked but in filter Data table activity when i tried filtering it didn’t work.

StrSplitDate1=03-04-24
strSplitDate2=04-04-24

[vrdabberu (Varunraj Dabberu)]

Hi @quick_123

You need to write like

DateTime.ParseExact(StrSplitDate1.ToString(), "MM-dd-yy", Globalization.CultureInfo.InvariantCulture)

Int he same way for strSplitDate2

DateTime.ParseExact(StrSplitDate2.ToString(), "MM-dd-yy", Globalization.CultureInfo.InvariantCulture)

Regards

[quick_123]

I Tried this, but I am getting the following error:

System exception.String ‘04-02-2024’ was not recognized as a valid DateTime. at Source: Filter Data Table

[vrdabberu (Varunraj Dabberu)]

Hi @quick_123

What are all the dare formats you have in Excel. Can you share the screenshot of the excel so that I can help you.

Regards

[Ajay_Mishra (Ajay Mahendra Mishra)]

@quick_123

StrSplitDate1=03-04-24
strSplitDate2=04-04-24

Hey @quick_123

Date1 & Date2 are in which format “dd-MM-yy” OR “MM-dd-yy” Format?

And as much I understood, you want to filter your input table with the date column!
Dates which are greater than Date1 & Smaller than Date2 right?

Or you want only last 1 month date rows as a output?

The more you clearify the better you’ll get the solution!

Regards,
Ajay Mishra

[quick_123]

No this is just an example, actually I want to extract rows from datatable between date1 and date2

[quick_123]

actually, the date formats are in format dd/MM/yyyy but in my case it is of format dd-MM-yy

[vrdabberu (Varunraj Dabberu)]

Hi @quick_123

If possible can you share the excel file.

Regards

[Ajay_Mishra (Ajay Mahendra Mishra)]

Hey @quick_123

I have solved your query just answer some of the question. So, that I can provide you a final LinQ.

Steps to Follow:
Just debug your xaml after reading the excel data just go to immediate panel and put Dt_Datatable(0)(1).ToString hit Enter → In this Dt_Datatable is input datatable variable name, (0) denotes Row And (1) denotes Column i.e Date Column in excel.

Try to check values of some rows of Date Column by changing Row Index in Immediate panel and confirm all the rows have same format. i.e Dt_Datatable(1)(1).ToString, then Dt_Datatable(2)(1).ToString then, Dt_Datatable(3)(1).ToString

Questions:

1. Confirm format of Date Column Values (Output of above steps is coming in ddMMyyyy format or MMddyy format or anyother…)
2. Confirm StrSplitDate1 & strSplitDate2 values(Final Format)

Screenshot would be appreciated of Immediate panel…!

Thanks & Regards,
Ajay Mishra
RPA Developer