Load only the two latest files

[KaisGarci]

Hello everyone, 

I have files ( All of them are in the same format) named as for example 'REAL_1008' and the '1008' is the date as the 10th of August.  I want only load the two latest fles based on that date. 

Can anyone help me please ? 

Thank you

[martinpohl]

Hello

take this:

for each vFile in filelist('YourPath\REAL_*')

Files:

load 

'$(vFile)' as File,

filetime() as Timestamp

autogenerate (1);

next

Files_Sorted:

noconcatenate load * resident Files order by Timestamp DESC;

drop table Files;

for vLoadfiles = 0 to 1

let vLoadfile = peek('File'),$(vLoadfiles),'Files_Sorted)';

load *

from, '$(vLoadfile )'

;

next

drop table Files_Sorted;

I would take filetime instead of numbers in filename because there is no year in.

Regards

[KaisGarci]

Thank you for helping me. 

But it didn't work. 

[martinpohl]

there is a script error

change

let vLoadfile = peek('File'),$(vLoadfiles),'Files_Sorted)';

to

let vLoadfile = peek('File'),$(vLoadfiles),'Files_Sorted';

Regards

[KaisGarci]

Hello @martinpohl 

Thanks for helping but it still give an error. 

[martinpohl]

let vLoadfile = peek('File',$(vLoadfiles),'Files_Sorted');

[KaisGarci]

It load but it gives me an empty table

[Kushal_Chawda · Accepted solution]

try below.  Change the path as per your folder location

for Each vFile in FileList('C:\REAL_*.xlsx')

Files:
LOAD subfield('$(vFile)','\',-1) as FileName,
date(date#(mid('$(vFile)',index('$(vFile)','_',-1)+1,4),'DDMM')) as Month,
'$(vFile)' as Path
AutoGenerate 1;

next

Files_needed:
NoConcatenate
first 2 LOAD *
Resident Files
Order by Month desc;

DROP Table Files;

Data:
LOAD * Inline [
Temp ];

for i=1 to FieldValueCount('Month')

let vFilePath = FieldValue('Path',$(i));

Concatenate(Data)
LOAD *,
FileBaseName() as FileName
FROM
[$(vFilePath)]
(ooxml, embedded labels, table is Sheet1); // you can change this highlighted part as you may have different way of loading the excel files

NEXT

DROP Table Files_needed;

DROP Field Temp;

[KaisGarci]

Thank you @Kushal_Chawda . this worked perfectly, but I want to add a variable called date which gives me the date of the 2 latest file. 

Thank you

[Kushal_Chawda]

you can add one variable as highlighted below in part of the code

for i=1 to FieldValueCount('Month')

let vFilePath = FieldValue('Path',$(i));

let vMonth = FieldValue('Month',$(i));

Concatenate(Data)
LOAD *,
FileBaseName() as FileName,

'$(vMonth)' as MONTH
FROM
[$(vFilePath)]
(ooxml, embedded labels, table is Sheet1); // you can change this highlighted part as you may have different way of loading the excel files