Extract newest (max) date from datatable

Help
1

Hi there,

I’m trying to extract the newest date from a Datatable using the following expression when assigning to a variable:

ExtractDataTable.AsEnumerable().Max(Function (drRows) If(DateTime.TryParse(drRows.Item(“Column-8”).ToString, Nothing), DateTime.Parse(drRows.Item(“Column-8”).ToString), DateTime.MinValue)).ToString

The expression takes column 8 where dates are stored and checks for the max date in the row, if a row contains a value which is not a date then it passes it as a Min value.

The problem i’m facing is that the expression seems to be handling the date in US format (mm/dd/yyyy) while I need it formatted as (dd/mm/yyyy).

Questions:

  1. How do I implement a formatting into the expression? If not possible, would it then make sense to use ParseExact and how would that then be done for the same purpose?

  2. Is it then possible to extract from the same row but from a different column?

Sincerely,
Reynir

2

To parse the date in dd/mm/yyyy, you will need to use ParseExact I think.
https://forum.uipath.com/search?q=parseexact

Hope that helps.

Regards.

3

There are a couple approaches I think.

One way is to take your Max value you calculated, then find the row where it equals that value.
ExtractedDataTable.AsEnumerable.Where(Function(r) If(DateTime.TryParse(r(“Column-8”).ToString, Nothing), DateTime.ParseExact(r(“Column-8”).ToString,"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture) = maxDate, False) ).First

Or another way, is to abandon using the .Max() method and instead use the .OrderByDescending() method.
ExtractedDataTable.AsEnumerable.OrderByDescending(Function(r) If(DateTime.TryParse(r(“Column-8”).ToString, Nothing), DateTime.ParseExact(r(“Column-8”).ToString,"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture), DateTime.MinValue) ).First

So essentially, you would sort it by the date value and take the first one, which would be the latest date.

Both approaches will return a data row in which you can extract any column within that data row.

Hopefully, I didn’t make any mistakes and hope this helps get your answer.

Regards.

3 Likes
4

@ClaytonM, thank you so much the reply, much appreciated!

I tried the .OrderByDescending method you recommended but that didn’t seem to exclude the first row which is the header from the scraped table, and just gave that as a result.

I’ve tried working around your answer and came to the following conclusion:

ExtractDataTable.AsEnumerable.Max(Function (drRows) If(DateTime.TryParse(drRows.Item(“Column-7”).ToString, Nothing), DateTime.ParseExact((“Column-7”).ToString,“DD/MM/YYYY”,System.Globalization.CultureInfo.InvariantCulture), DateTime.MinValue)).ToString

However, now I get the error that Column-7 doesn’t belong to the table. Do you have any idea on what could be the problem? It seems that it just runs through the column while excluding everything.

5

Hey @rjonasson

String Inputdatestring="12/18/2018 //en-US culture format

String Datevalue = DateTime.ParseExact(Inputdatestring, “MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy”) 

//Output - 18/12/2018

Regards…!!
aksh

1 Like
6

Oh. You can get rid of all non-date values using the .Where()
ExtractedDataTable.AsEnumerable.Where(Function(r) DateTime.TryParse(r(“Column-8”).ToString, Nothing) ).OrderByDescending(Function(r) If(DateTime.TryParse(r(“Column-8”).ToString, Nothing), DateTime.ParseExact(r(“Column-8”).ToString,"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture), DateTime.MinValue) ).First
If you try that again, let me know if you have any other issues.

If I were you, I would output your Extracted table and verify the data, by either using Write CSV or Output Data Table to a string in a Write Line… just so you can see what the column names are.

Regards

7

I am trying to do the same thing although I only need the date. When I use this format I get the error, “‘AsEnumerable’ is not a member of ‘System.Data.DataTable’”. That is the variable format I have seen in several examples. Any idea why this isn’t working?

8

Sometimes copy/paste 'ing code in can cause this, which I normally say to erase manually and type it back in manually to see if works.

If not, then it could be the version of your Studio or .net.

If the version is up to date (2019 or later), then I’m an unsure, but you can post a screenshot of your expression editor with the error to see if I can recognize the problem.

Alternatively, you can use .Select rather than .AsEnumerable to have the same result.
dt1.Select.Where(Function(r) ..... )

Regards

9

Thanks for your quick response Clayton. I just tried manually typing it and I get the same result. I am currently using Studio 19.10 although we are updating to 20.10 in a couple of weeks. I am attaching the error. Thanks for your help.

Recent Date Error
Recent Date Error1365×148 45.3 KB

10

You could check your .net framework version too.

Also try using .Select instead of .AsEnumerable