I am not able to filter on date in a data table

Help Studio
, ,
1

Hello Friends,

I have situation where I download data from SAP and in the data table I do a filter on the date for a certain condition there is no output in the new data table.

image
image808Ă—415 16.6 KB

The Grn Date is in TEXT format when it is downloaded. I changed it to DATE format or to General and tried it did not work for me.

I am attaching a sample file.
Test_Date.xlsx (9.8 KB)

Appreciate your valuable tips/thoughts on the same.

Regards,
Manjesh

2

If “Grn Date” is a datetime (which is what you want) then you don’t do .ToString(“MM/dd/yyyy”) in the Value box. Just do getLocalDateTime (which is a datetime, not string, value). You have to compare like datatypes - so both the Column and the Value should be datetimes.

3

@manjesh_kumar - Please check this…

image
image1098Ă—755 51.5 KB

GrnDate_MK.xaml (8.5 KB)

Note: After reading your sheet, first use a for each and print the date column value to see how it is printing first. Based on that you have to decide your code for Datetime.parseexact, it will always easy if you do this way.

image
image1079Ă—423 21.7 KB

1 Like
4

Hello @prasath17 ,

Thank you for this hint, however this is adding burden to my program which has increased by 7 minutes. The reason is that it has to loop through 18000 records, and doesn’t get better as the year progresses.

Is there an alternate method which can fulfil this requirement and reduce the time.

Regards,
Manjesh

5

@manjesh_kumar - you didn’t use the LINQ query screenshot which I provided ? The for each loop thing is just to show you how the date format works for the data you shared.

6

Hello @prasath17 ,

I tried the linq query, it is giving an error.
image

image
image1184Ă—478 44.1 KB

Regards,
Manjesh

7

@manjesh_kumar - Whatever the date you provided it is working fine for me…
GrnDate_MK.xaml (12.0 KB)

As i mentioned in the steps clearly, please print the date from the column and see how it is printing, based on the use the datetime.parseexact…

8

Hello @manjesh_kumar
you can try this—
ThirdPartDT.AsEnumerable.Where(function(r) Convert.ToDateTime(r(“Grn Date”).ToString)>GetLocalDateTime).ToArray.CopyToDataTable

or
this—

ThirdPartDT.AsEnumerable.Where(function(r) DateTime.ParseExact(r(“Grn Date”).ToString,“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture)>GetLocalDateTime).ToArray.CopyToDataTable

9

Hello @pravupada ,

the same error is repeating. I tried both.

Regards,
Manjesh

10

Can you show this??? and did you ran the code against the sample file you shared…it is working fine for me…

11

Hello @prasath17

Please find the screen shot as well as the original file where I have deleted the sensitive information and I have preserved the original format.

image

image
image768Ă—486 23.9 KB

Ageing - Copy.xlsx (205.2 KB)

Regards,
Manjesh

12

@manjesh_kumar - If this is your orignal file, I see some blank rows here…

If you have blank rows, none of of code will work. You have to sort the blank rows first by keeping only the rows with values and then apply LINQ query…

Will send you the code shortly…

Note: you never mentioned that you column has blank values…

13

Hi @manjesh_kumar

Try this Linq query, hope this will helps…

dt1.AsEnumerable().Where(Function(row) row(“Grn Date”).ToString>getLocalDateTime.ToString(“MM/dd/yyyy”) and row(“Grn Date”)<>“”).CopyToDataTable

If this does not work try this please

dt1.AsEnumerable().Where(Function(row) row(“Grn Date”).ToString>getLocalDateTime.ToString(“MM/dd/yyyy”) and row(“Grn Date”)<>" ").CopyToDataTable

Mark it as solution if it helps you

Regards,
Nived N

14

Hello @manjesh_kumar
i saw some date fields are empty in the sheet So update the LINQ to this

ThirdPartDT.AsEnumerable.Where(function(r) Not String.IsNullOrEmpty(r(“Grn Date”).ToString) AndAlso Convert.ToDateTime(r(“Grn Date”).ToString)>GetLocalDateTime).ToArray.CopyToDataTable

or

ThirdPartDT.AsEnumerable.Where(function(r) Not String.IsNullOrEmpty(r(“Grn Date”).ToString) AndAlso DateTime.ParseExact(r(“Grn Date”).ToString,“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture)>GetLocalDateTime).ToArray.CopyToDataTable

Thanks.

15

Dear’s

I had a discussion with @prasath17 and he mentioned that the data which I had sent contains.

  1. BLANK ROWS
  2. The date is in dd/MM/yyyy
  3. The date is in dd.MM.yyyy
  4. The date is in MM.dd.yyyy
  5. The date is in MM.dd.yyy

image

Please check the Ageing - Copy.xlsx

image

@pravupada @NIVED_NAMBIAR

Regards,
Manjesh

16

@manjesh_kumar - From the screenshot itself i can see 1 more format

MM/dd/yyyy - 12/18/2020

yyy is not a proper date format so it will fail.

17

Hi @manjesh_kumar
Did u tried the way as i suggested. I hope that even the dates in excel is in different format, but printed in uipath by default without any specification in format is always as MM/dd/yyyy (if u used ToShortDateString extension too )

18

Hello @prasath17 ,

Hope you are doing good.

I was off for 2 weeks due to some other priority tasks.

  1. I got the data cleansed from the user and the linq works as expected, however if it does find any rows which are not greater than the current date, then it throws an error "Assign: The source contains no DataRows." how do I resolve this.

  2. I need a support on another topic where in column “A” if the value is “T9” then my value in column “B” should be “I love UIPATH” using LINQ only since I don’t want to use for each because my file contains more than 20000 rows.

I also thank @NIVED_NAMBIAR @pravupada for your valuable suggestions.

Regards,
Manjesh

19

Hi @manjesh_kumar
for your first question, how u need to filter the dates by current date?

20

Hello @NIVED_NAMBIAR ,

It should handle the null case, when there are no rows which is not greater that current date then , it should not do anything and allow me to continue with my workflow.

As suggested by @prasath17

(from r in dt1.AsEnumerable where datetime.Parse(r(2).tostring) > now select r).CopyToDataTable

Regards,
Manjesh