How to get the max date from a set of files?

[microwin88x]

Hello!

I have a folder named Selections in C:\Users\Mic\Documents\Selections\ where I have files with this format:

File_DD_MM_YYYY_HH_MM_SS.csv

For example:

File_03_07_2012_17_25_30.csv

File_15_10_2012_12_40_25.csv

File_07_05_2013_08_15_43.csv

File_01_03_2014_11_18_12.csv

File_29_08_2013_09_30_27.csv

I need to read those filenames from the folder to get the file with the max date.

In this case, it'd be: File_01_03_2014_11_18_12.csv

Then I'd need to read it's content.

How could I do that?

Thank you!!!

[lukaspuschner]

Hello,

use FileName() as [Fieldname] in your loadstatement to create a field with your Filenames

[jonasheisterkam]

Hi,

you can also use "for each File in filelist ('Path\*')" and parse $(File) in a timestamp. If $(File) is newer or you have no Value in your Variable X then drop $(File) in it. After the loop X has the newest filename.

[#4]

After this:

let v_file=;

For each Ext in FileList('C:\Users\Mic\Documents\Selections\*.csv')

let v_file = if ('$(v_file)'<'$(Ext)','$(Ext)','$(v_file)');

NEXT;

you will have max file name in v_file variable.

You must modify condition to check date part of filename....

regards

Darek

[microwin88x]

I found the following code:

Tab1:

LOAD

FileBaseName() as filename,

Timestamp(Timestamp#(Right(FileBaseName(),19),'DD_MM_YYYY_hh_mm_ss'),'DD/MM/YYYY hh:mm:ss') as Date

FROM

C:\Users\Mic\Documents\Selections\File_*.csv //Put * here to load all file in folder

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Maxdate:

LOAD

MaxString(filename) as Mdate

Resident Tab1;

LET vMaxFileName = peek('Mdate',-1,'Maxdate');

DROP Table Tab1;

New:

LOAD

*

FROM

C:\Users\Mic\Documents\Selections\File_*.csv

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

Where filename = $(vMaxFileName);

---

But the MaxString doesn't get the filename for the max date file, it brings me File_29_08_2013_09_30_27.csv, that is the file with the max day...

[jonasheisterkam]

use Date in Maxdate and sort the table on it. Then peek the last one

[jonathandienst]

Hi

This is what I use:

Let vMaxDate = 0;

For Each vFile in Filelist('C:\Users\Mic\Documents\Selections\Dime_*.csv')

  Let vFileBase = Subfield(vFile, '\', -1);

  Let vDate = Date#(Mid(vFile, 6, 19), 'DD_MM_YYYY_HH_MM_SS');

  If vDate > vMaxDate Then

  Let vMaxDate = vDate;

  Let vMaxFilePath = vFile;

  End If

Next

Data:

LOAD *

FROM [$(vMaxFilePath)]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

HTH

Jonathan

[its_anandrjs]

Hi microwin88x,

I change some changes in the load script that i provide earlier to you but it is for *.xlsx file may be some problem in CSV please check that but from below code i load latest xlsx file

Tab1:

LOAD

ColA,FileName() as filename,

Timestamp(Timestamp#(Right(FileBaseName(),19),'DD_MM_YYYY_hh_mm_ss'),'DD/MM/YYYY hh:mm:ss') as Date

FROM

D*.xlsx

(ooxml, embedded labels, table is Sheet1);

Maxdate:

LOAD

MaxString(filename) as Mdate

Resident Tab1;

LET vMaxFileName = peek('Mdate',-1,'Maxdate');

DROP Table Tab1;

New:

LOAD *

FROM

$(vMaxFileName)  //An put variable here

(ooxml, embedded labels, table is Sheet1);

Regards

[its_anandrjs]

Hi microwin88x,

Check now i updated and it is working now for the CSV file it self by using the Filename() and max variable use below script for loading latest CSV files from the folder.

Tab1:

LOAD

FileName() as filename,

Timestamp(Timestamp#(Right(FileBaseName(),19),'DD_MM_YYYY_hh_mm_ss'),'DD/MM/YYYY hh:mm:ss') as Date

FROM

Dime*.csv

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

Maxdate:

LOAD

//Timestamp(Max(Date),'DD/MM/YYYY hh:mm:ss') as Mdate

MaxString(filename) as Mdate

Resident Tab1;

LET vMaxFileName = peek('Mdate',-1,'Maxdate');

DROP Table Tab1;

FinalLoad:

LOAD *

FROM

$(vMaxFileName)

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

EXIT Script;

Regards

[microwin88x]

I like the solution, but do you know why do I get the following result?

For the files:

Dime_01_04_2014_17_23_17.csv

Dime_01_04_2014_17_24_23.csv

Dime_05_07_2014_10_46_01.csv

Dime_05_10_2014_10_46_01.csv

Dime_23_04_2014_10_39_34.csv

I get:

vMaxFilePath: C:\Users\Mic\Documents\Selections\Dime_01_04_2014_17_23_17.csv