Loading Excel Files with Specific Names

podge2019 — Fri, 21 May 2021 14:33:47 GMT

Hello,

Looked on multiple previous posts and unfortunately cannot find what I'm looking for so looking for assistance.

The current app that I have loads an Excel File for a Specific country with maybe 25k lines on it.

The app now needs to load an additional country and I'm having a slight issue loading the file cleanly.

Currently I load a file path and name as below: - pretty straightforward...

Filename - FR_Commission_Report Pay Date 202105_16.xlsx

FROM
[FQDN:\Path1\Path2\*FR_Commission_Report Pay Date*.xlsx]
(ooxml, embedded labels, table is [Sheet1]);

In the location above files are automatically produced the for multiple Countries.

Now I need to pick up another file called ES_Commission_Report Pay Date 202105_16.xlsx

I know I could pick up all the files if I just use * Commmission_Report*, however, I don't want to load in all this unnecessary data.

I can also do a concatenate in my script but that just seems to be long winded when bringing in 40 columns headers unless there is a cleaner way to do it.

Basically is there a way to load file names containing FR and ES?

Could anyone provide guidance please?

Thanks

Re: Loading Excel Files with Specific Names

JuanGerardo — Fri, 21 May 2021 16:50:42 GMT

Hi @podge2019, I suggest using for each file statement. Something like this (probably you will get some typos because I don't remember the exact syntax):

for each File in filelist ([FQDN:\Path1\Path2\*_Commission_Report Pay Date*.xlsx]) If Left('$(File)', 2) = 'ES' or ... LOAD '$(File)' as FileName, Other fields... FROM [FQDN:\Path1\Path2\$(File)] (ooxml, embedded labels, table is [Sheet1]); End If next File

topic Re: Loading Excel Files with Specific Names in QlikView

Loading Excel Files with Specific Names

Re: Loading Excel Files with Specific Names