topic Re: How to load the latest excel file in a folder? in QlikView

How to load the latest excel file in a folder?

ffan2008 — Sat, 16 Nov 2024 18:35:49 GMT

I am trying to store the name of the latest filename from a batch of files in the folder and recall it when I am loading.

That way I can always load the latest file clicking "reload" without typing in the latest file name again.

I have used this code to extract the file names

load_name:
load *, filename() as Filename

From *.xlsx (txt);

and not sure how to recall that latest file name in load.

Order:
LOAD *

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

Would be great if someone can give me a hand.

Thank you very much!

Re: How to load the latest excel file in a folder?

bhaskar_sm — Sun, 24 May 2020 17:34:35 GMT

Hi, you may try something like this.

TablesList:
LOAD
"TableName"
FROM [C:/TablesDetails.csv]
(txt, utf8, embedded labels, delimiter is '\t', msq);

For i = 0 to (NoOfRows('TablesList')-1);
Let varTableName = Peek('TableName',$(i), 'TablesList');

// Load Tables

$(varTableName):

Load

FROM FROM
[C:\Data\$(varTableName).xlsx]
(ooxml, embedded labels, table is Orders);

Next;

Thanks,

Bhaskar

Re: How to load the latest excel file in a folder?

Saravanan_Desingh — Thu, 28 May 2020 00:04:28 GMT

One solution is..

For Each File in FileList ('C:\Users\...\MyFiles*.xls') Folder: Load '$(File)' as Name, FileTime( '$(File)' ) as FileTime Autogenerate 1; Next File //Method 1 - LatestFile: First 1 Load Name, FileTime, 1 as dummy Resident Folder Order By FileTime DESC; Let vFile=Peek('Name',0,'LatestFile'); Trace << vFile=$(vFile);

Re: How to load the latest excel file in a folder?

Saravanan_Desingh — Thu, 28 May 2020 00:05:12 GMT

one more..

//Method 2 - Modified_Time: Load Timestamp(max(FileTime)) as ModifiedTime, FirstSortedValue(Name, -FileTime) as LastModifiedFileName Resident Folder; Let vModifiedTime=Peek('ModifiedTime',0,'Modified_Time'); Let vLastModifiedFileName=Peek('LastModifiedFileName',0,'Modified_Time'); Trace << vModifiedTime=$(vModifiedTime); Trace << vLastModifiedFileName=$(vLastModifiedFileName);

Re: How to load the latest excel file in a folder?

Saravanan_Desingh — Thu, 28 May 2020 15:09:41 GMT

Did you try the above solutions?