Pick latest file from Drive

[qlikwiz123]

Hi,

I have files being saved everyday in a folder and the names of the files are date and time stamped as below

2020-07-20 10-51 Data.xlsx

2020-07-21 12-22 Data.xlsx

2020-07-22 14-56 Data.xlsx

2020-07-23 10-09 Data.xlsx

2020-07-24 18-32 Data.xlsx

2020-07-25 17-54 Data.xlsx

The format is YYYY-MM-DD HH-MM

I tried some ways but I think due to this unconventional datetime stamp, it is not working

How do I pick up the latest file every time I run the script?

[fosuzuki]

Hi,

you can use this code to generate a list of the files:

for each vFile in FileList('C:\users\username\documents\*.xlsx')

Files:

LOAD '$(vFile)' AS File,

timestamp(timestamp#(left(SubField('$(vFile)', '\', -1),16), 'YYYY-MM-DD hh-mm')) AS Timestamp

AutoGenerate(1);

next

Then, work on the table to get the newest file.

[qlikwiz123]

Hi,

This only gets all the files listed in the Directory under 'File' and timestamps under 'Timestamp' columns. But I need to load only the file with maximum date on it. Also, there are other files in the location and hence I only need to do this for the files that end with 'Data' (Example File name: 2020-07-28 14-33 Data.xls)

[Kushal_Chawda · Accepted solution]

try below. Change the folder path to your actual folder location

Data:
first 1 LOAD FileBaseName() as FileName,
timestamp#(trim(purgechar(lower(FileBaseName()),'data')),'YYYY-MM-DD hh-mm') as Time
FROM
[C:\*Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Max:
load FirstSortedValue(FileName,-Time) as Max_Time_FileName
Resident Data;

DROP Table Data;

let vMax_Time_File_Name =Peek('Max_Time_FileName',0,'Max');

Data:
LOAD *
FROM
[C:\$(vMax_Time_File_Name).xlsx]
(ooxml, embedded labels, table is Sheet1);

[qlikwiz123]

Thank you 🙂

[qlikwiz123]

H @Kushal_Chawda 

I don't see it picking up the file at all. And hence the variable is empty and the file reload fails. Here are my files that I am using to test.

[Kushal_Chawda]

Hi,

You have blank excel. Please put some data. I assumed that excel file should have data.

[qlikwiz123]

Got that, thank you