topic Re: Getting Filetime from multiple excel sheets in QlikView

Getting Filetime from multiple excel sheets

JohnSamuel123 — Thu, 20 May 2021 09:26:03 GMT

hi experts,

hopefully an easy one.

i need to pull in just the filetimes of multiple excel sheets in different folder locations. what would be the most efficient way to do this in the script?

i dont want to load any data within the excel sheets, just each excel sheets filetime (i then get the max file time later in the script)

i have a few different tables that i concatenated together. however it takes a few minutes to load in the few hundred excel sheets as i think im loading in all the lines in the excel sheet as well as the filetime

my table looks like this:

Table:
Load
filetime() as filetime
FROM x

(ooxml, embedded labels, table is POBA);

is there a better way to just grab the filename and not any of the sheet data?

thanks,

Re: Getting Filetime from multiple excel sheets

Or — Thu, 20 May 2021 10:50:05 GMT

Have a look at using FileList() and loops, e.g.

https://community.qlik.com/t5/QlikView-App-Dev/for-each-file-in-filelist/m-p/344604

Since you have different locations you may need to use a second loop to cover all of those. The post in question also shows how to get information from the file without reading the contents.

Re: Getting Filetime from multiple excel sheets

JohnSamuel123 — Thu, 20 May 2021 11:11:25 GMT

Hi @Or thanks for that,

that code is exactly what i need, you mentioned using a second loop to capture all the folder locations. would you have an idea on how to do this? it works if i create a single loop for each of my file locations and then just have qlik join the fields together, but thats a lot of code and im sure theres an easier way,

is it possible for the "in filelist" section to have all my folder locations in and not just one location per loop?

thanks

Re: Getting Filetime from multiple excel sheets

Or — Thu, 20 May 2021 11:17:51 GMT

Your second loop would contain the list of folders to iterate over, similar to the file extension loop in the example I linked. In pseudo code terms,

for each FolderName in 'c:\temp', 'd:\temp', 'c:\windows'

for each File in filelist (FolderName &' \*.*')

Do stuff

Next File

Next Foldername

Re: Getting Filetime from multiple excel sheets

JohnSamuel123 — Thu, 20 May 2021 11:35:37 GMT

Hi @Or ,

im getting this syntax error semantic error.The control statement is not correctly matched with its corresponding start statement next FolderName

this is my code:

for each FolderName in ('C:\2021*.xlsx', Network:CD\*.xlsx', D:\Earned Hours\*.txt') for each File in filelist (FolderName &' \*.*') table: FileTime( '$(File)' ) as filetime autogenerate 1; next File next FolderName;

would you know where im going wrong?

thanks,

Re: Getting Filetime from multiple excel sheets

Or — Thu, 20 May 2021 11:37:26 GMT

Your folder names already contain the extension, so you should remove the &' \*.*' part of the code...

Otherwise, as always when working with loops and variables, I recommend using debug mode to step through each line. This lets you see if the variables are being set and read correctly and should help you pinpoint any issues.