How to load latest file to load script?

[jim_chan]

Hi guys,

i have file to load into the loading script, but however, i want to load in the LATEST one only. For exmaple:

i have 2 files: ABC_20181015.csv , ABC_20181018.csv

But i only want to load in the latest(ABC_20181018) only. not per today's date, should follow the latest date of the file(ABC_20181018)

How should i  write in script??

Load

*

from ???????

Rgds

Jim

[jonathandienst · Accepted solution]

Loop over the files like this:

Let vMaxDate = 0;

Let vFileToLoad = '';

For Each vFile in ('ABC_????????.csv')

    Let vFileDate = TextBetween(vFile, 'ABC_', '.csv');

    If vFileDate > vMaxDate Then

        vFileToLoad = vFile;

        vMaxDate = vFileDate;

    End If

Next

Data:

LOAD *

FROM [$(vFileToLoad)] (txt, ...... <other file attributes here> ....;

[jim_chan]

Dear Jonathan,

For Each vFile in ('ABC_????????.csv') - just use ? mark?

[Anil_Babu_Samineni]

Try this way?

File_Names:

LOAD

Date(Date#(TextBetween(FileName(),'_','.'),'YYYYMMDD')) as Date_Extract

FROM Path../ABC_*.csv

(txt, utf8, embedded labels, delimiter is ',', msq);

Max_Date:

Load

Max(Date_Extract) AS Max_Day

Resident File_Names;

LET vMax_Day=DATE(PEEK('Max_Day',0,'Max_Date'),'YYYYMMDD');

LOAD *

FROM ABC_$(vMax_Day).csv

(txt, utf8, embedded labels, delimiter is ',', msq);

[jonathandienst]

The ? is a wild card  for a single character. So the 8 ? marks is to match the 8 digit date value in the file name.

[ffan2008]

Is it possible to load the file names into a table, as such

Spoiler

load_name:
load *, filename() as Filename

From *.xlsx (txt);

and load the last file name.

Spoiler

.....

FROM
[C:\Data\orders-2020-05-21.xlsx]
(ooxml, embedded labels, table is Orders);

How do I insert the last value into the file name?