Dynamically load available month wise excel files

[Aditya_Chitale]

Hi All,

I have a requirement where I have to load only the available excel files from a monthwise folder location.

for eg. In Nov 2022 folder, if I have 3 excel files (ABC,XYZ,PQR), then I have written load statement for those 3 files. But if in dec 2022 folder (which will be used to load  files for dec 2022 month), there are only 2  files (ABC,XYZ), then how can I load only those 2 files without getting "file not found" error for file PQR ?

Edit: Sorry, I forgot to mention that the field names in the files might have different names. eg. ABC file can have column header as 'Value' for Sales figures. But for the same, XYZ might have header name as 'Amount'. So cannot use for each loop.

Regards,

Aditya

[Or · Accepted solution]

Lots of options, depending on the specific scenario...

The quick-and-dirty option would be to use Set ErrorMode=0; to ignore any errors (including missing files).

If files share the same structure, you could load * from the folder rather than load each file individually.

You could also use e.g. FileSize([filename])>0 to check if the file exists, and only load from the file if it does.

You might find this useful insofar as looping through multiple folders to read files:

https://community.qlik.com/t5/New-to-Qlik-Sense/loop-through-folders-and-sub-folders-to-load-multipl...

[Aditya_Chitale]

Hi @Or . Thanks for the reply. But I cannot use error mode method as we are already using error condition to control the distribution of reports from Nprinting (don't send report if error).

As for loading all files with * , the file structure might vary. The only way I could see now is by using filesize function. Can you please tell me how to check file size for a group of files separated date wise ?

for eg. in my monthwise folder for Dec 2022, if I have ABC 01-12-2022.xlsx, ABC 02-12-2022.xlsx, ABC 03-12-2022.xlsx. All these ABC files have common structure.

Even if one of the ABC files contains data, I want to load all of them. And the preceding dates of file name may vary when  the month changes.

Regards,

Aditya

[Or]

Regarding errormode, you would just use it for this section, and then reset it to 1.

Insofar as using FileSize, I'd suggest you have a look at the top post in the thread I linked, or generally search for looping through multiple subdirectories, for approaches to doing this. I'm not sure if you'll find the exact thing you're trying to do but you should be able to get the general gist and syntax required.

It seems your question regarding multiple flies is an entirely different one from what you originally posted, but you could just load the entire set of files from that folder (regardless of whether or not they have rows) and then count the number of rows in the concatenated table to see if any of them had rows.

[RudyKostka · Accepted solution]

FACT_Data:
LOAD * Inline [
FileBaseName
];

SET vDirPath = 'lib://DirPath/';

FOR EACH vFile in filelist ('$(vDirPath)'&'*.xlsx')

LET vFileName = Mid('$(vFile)', Index('$(vFile)','/',-1)+1);

[$(vFileName)]:
Concatenate (FACT_Data) LOAD
FileBaseName() as FileBaseName,
*
FROM [$(vFile)]
(ooxml, embedded labels, table is Sheet1);

NEXT vFile;

LET vDirPath = Null();
LET vFile = Null();
LET vFileName = Null();

[Aditya_Chitale]

Will try the solution from link and let you know if it worked.

Regards,

Aditya

[Aditya_Chitale]

Thanks for the solution. I solved this using filesize() function with 'if' condition.

Regards,

Aditya