Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
podge2019
Contributor III
Contributor III

Loading Excel Files with Specific Names

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

Labels (3)
1 Reply
JuanGerardo
Partner - Specialist
Partner - Specialist

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

JG