Filter a datatable to get the latest file that is uploaded in a site

Help
1

I need a help to filter a data table which contains the file names and dates from a site. I want to filter the most recent file that is uploaded in the site. how can I find that.

image
image785Ă—298 19.6 KB

2

Hi,

you can compare the column with Now and based on the difference you can get the mostg recent file.

Let us know if more info needed
Regards,
Pavan H

3

I want the file that is uploaded most recently. how can i compare with the previous file timestamp?

4

Hi,
As per the table that you have given in the flow which has published time. if you can try comparing from there you will get the expected result.

Regards,
Pavan H

5

EDIT: Ignore the stuff I posted and just check out the link below. It’s a great example of how to do it with a good explanation of how it works as well.

Start by reading the entire excel sheet to a datatable (i’ll call it dt1)

Next you should make sure the datatable is reading the column named “published” as a datetime by using datetime.Parse or datetime.ParseExact.

Finally, for the actual filter/comparison you could do it multiple ways. I’d recommend converting dt1 to enumerable and using the .OrderByDescending() function. You could also use an Enumurable.Max(), or you could use dt1.Select(), or you could loop through each row and compare to the current highest max. All would work

You could also combine the parsing and the functions into one single line if you choose so it’d look something like this (note, it’s a bit messy and I haven’t actually tried it out myself, just typing it out):

dt1.AsEnumerable.Where(Function(x) DateTime.TryParse(x(“published”).ToString, Nothing)).OrderByDescending(Function(x) If(DateTime.TryParse(x(“published”).ToString, Nothing), DateTime.ParseExact(x(“published”).ToString,"dd.MM.yyyy HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture), DateTime.MinValue)).First
6

i am also having data table. is this applied for the entire table or for each row? because i don’t understand what is data row in that function. could you please have a look and update??

7

If you save your datatable and name it “dt” (without quotes) the function in that thread i linked will work.

Create a datetime variable called maxdate, and put that in an assign activity

8

I have used the datatable which is scraped from website. it is named as extractDT

9

Put this in an assign activity where MaxDate is a datetime variable

Assign MaxDate = Convert.ToDateTime( ((from DataRow dr in extractDT.Rows orderby Convert.ToDateTime(dr["DateColumn"]) descending select dr).FirstOrDefault()["DateColumn"] ) )

10

This is what i tried…It has some errors for me. I had asked the snippet owner, and he is looking into it, Hope it will get worked :slight_smile:

11

Hi @neethulg,

try below code
Convert.ToDateTime( ((from DataRow dr in extractDT.Rows orderby DateTime.ParseExact(dr("published").ToString, "dd.MM.yyyy hh:mm:ss",System.Globalization.CultureInfo.InvariantCulture) descending select dr).FirstOrDefault()("DateColumn")))

Regards,
Arivu :slight_smile:

1 Like
12

Hi @neethulg,

use below code

dt1.AsEnumerable().Max(Function(row) DateTime.ParseExact(row("published").ToString, "dd.MM.yyyy hh:mm:ss",System.Globalization.CultureInfo.InvariantCulture))

Regards,
Arivu

1 Like
13

@neethulg See the response from @arivu96 who is the one who originally posted the code I linked.

His updated post here changed the datetime.ParseExact() so it is looking for the correct datetime format according to your example

EDIT: Note that the hh:mm:ss may need to be HH:mm:ss depending on whether the hours are in 24:00 or 12:00 increments. 2:00PM is shown as 14:00 then it should be HH:mm:ss but if it shows up as 02:00 then it should be hh:mm:ss

14

This is also showing error in extractDT.Rows.

15

it’s showing an error “String was not recognized as valid date time”

16

Hi @neethulg,

If its in 24 hour time format use below code

dt1.AsEnumerable().Max(Function(row) DateTime.ParseExact(row("published").ToString, "dd.MM.yyyy HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture))

If its in 12 hour time format use below code

dt1.AsEnumerable().Max(Function(row) DateTime.ParseExact(row("published").ToString, "dd.MM.yyyy hh:mm:ss",System.Globalization.CultureInfo.InvariantCulture))

Regards,
Arivu

1 Like
17

Thanks. Its working !!!

18

BlankProcess.zip (15.6 KB)

@neethulg i think this will answer your problem.

19

I got the solution. Anyway thanks !!!

1 Like
20

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.