How to load selection of xls files in folder with Qlik Sense

[miklodepiklo]

Dear all,

I'm having a list of Excel files in a folder. I'm trying to load a selection of these files. But I'm getting stuck here since a simple wildcard load isn't getting me anywhere because the names of the files look very alike.

Here's the list of files in the folder:

01022018 aantal op peildatum.xls

01022018 bedrijf 1 aantal op peildatum.xls

01022018 bedrijf 2 aantal op peildatum.xls

01022018 bedrijf 3 aantal op peildatum.xls

01022018 bedrijf 4 aantal op peildatum.xls

01022018 bedrijf 5 aantal op peildatum.xls

01022019 aantal op peildatum.xls

01022019 bedrijf 1 aantal op peildatum.xls

01022019 bedrijf 2 aantal op peildatum.xls

01022019 bedrijf 3 aantal op peildatum.xls

01022019 bedrijf 4 aantal op peildatum.xls

01022019 bedrijf 5 aantal op peildatum.xls

...

I want to load only the files that do not have the word 'company' in it. What's the best way to do this in Qlik Sense?

Thanks for helping!

[miklodepiklo]

Company = bedrijf

[marcus_sommer]

You could use a for each in filelist approach and within them you could check for the right files. It means something like this:

for each vFile in filelist('path\files*.xls')

     if not wildmatch('$(vFile)', 'bedrijf') then

          load * from $(vFile) (biff, ...);

     end if

next

More to the logic could you find in the help by for each or here: Loops in the Script.

- Marcus

[jonathandienst]

I think you need to add the wildcard asterisks here:

for each vFile in filelist('path\files*.xls')

     if not wildmatch('$(vFile)', '*bedrijf*') then

          load * from $(vFile) (biff, ...);

     end if

next

[marcus_sommer]

Of course you are right, I was too much in hurry ...

- Marcus

[jonathandienst]

[miklodepiklo]

Guys,

Thanks a lot so far. I tried using a loop but it's giving an error. Here's the script that I'm using now:

for each vFile in filelist('[lib://Qlik Sense datafolder (concern_lankhorm)/Proalert\* aantal op peildatum.xls]')

     if not wildmatch('$(vFile)', '*bedrijf*') then

          load * from $(vFile) (biff, embedded labels, Header is 5 lines);

     end if

next

exit script;

The error is:

Unexpected token: 'concern_lankhorm', expected one of: 'codepage', 'Comment', 'biff', 'dif', 'fix', 'html', 'json', ...

But I did set the (biff...) stuff. So I though it should work. It keeps complaining if I change it to (biff, no labels) for example.

Any ideas?

Thanks again,

Mike

[marcus_sommer]

There is no sheet specified within the file-format, something like ... table is Sheet1$ ...

- Marcus

[miklodepiklo]

Hi Marcus,

There's no sheet specified because the sheets all have different names. If I leave out the sheet names it used to work this way (when I don't use a for each loop).

Mike

[marcus_sommer]

I'm not absolutely sure yet but if I remember correctly then leaving the sheetname to get the first sheet worked by xlsx-files and by using xls-files it should be table is @1. Just give it a try.

- Marcus