topic Re: Loop through files in folder and load into same table in QlikView

Loop through files in folder and load into same table

arhadisqlik — Fri, 18 Oct 2019 09:57:18 GMT

Hi all

I'm struggling with a for each loop that should loop through a all subfolders in a given folder path and load content of excel files into the same table.

The problem is that everytime it iterates to a new file and starts the load it increments the tablename.

My code looks like this:

set vRoot = 'C:\QlikView Storage\Group88\Data\NewFileFomats\*';

for Each dir in DirList(vRoot)

for Each file in Filelist('$(dir)\*.xlsx')

tmp:
load
*
from
[$(file)\*.xlsx](ooxml, embedded labels, table is 'Import QW');

next;

Loadresult:
load * Resident tmp;

drop table tmp;

next;

As is there are 3 subfolders in the path C:\QlikView Storage\Group88\Data\NewFileFomats\* and it iterates through them all and loads the files but at the end I'm left with "temp-1" table, no Loadresult-table and it only has data from one file.

I know it is possible to load all files from a folder into the same table using something like

Tmp:

Load * from

[C:\QlikView Storage\Group88\Data\NewFileFomats\Loewe\*.xlsx]
(ooxml, embedded labels, table is Import QW);

But how do implement this behaviour in the loop?

Thanks in advance for the help.

Best regards

Re: Loop through files in folder and load into same table

Vegar — Fri, 18 Oct 2019 10:22:01 GMT

Try this:

set vRoot = 'C:\QlikView Storage\Group88\Data\NewFileFomats\*';
for Each dir in DirList(vRoot)
for Each file in Filelist('$(dir)\*.xlsx')
tmp:
load
*
from
[$(file)](ooxml, embedded labels, table is 'Import QW')
;
next file ;
next dir ;

Re: Loop through files in folder and load into same table

arhadisqlik — Fri, 18 Oct 2019 11:08:13 GMT

Hi Vegar

Thank you for the feedback.

Unfortunately it did not do the trick.

I ended up with four temp-tables (there are four files in three folders) tmp, tmp-1, tmp-2, tmp-3.

Best regards

Anders

Re: Loop through files in folder and load into same table

Vegar — Fri, 18 Oct 2019 12:03:09 GMT

I see, the autoconcatenate feature in doesn't seem to work. You'll need to explicit use the concatenate. See my suggestion below.

set vRoot = 'C:\QlikView Storage\Group88\Data\NewFileFomats\*'; tmp: LOAD * inline [ DummyField ] for Each dir in DirList(vRoot) for Each file in Filelist('$(dir)\*.xlsx') Concatenate (tmp) load * from [$(file)](ooxml, embedded labels, table is 'Import QW') ; next file ; next dir ; DROP FIELD DummyField

Re: Loop through files in folder and load into same table

arhadisqlik — Fri, 18 Oct 2019 12:25:21 GMT

THANKS!

It was exactly this I was looking for.

Didn't have the imagination to just create a dummy field in the tmp table making it possible to use the concatenate.

Enjoy the weekend!

Best regards

Re: Loop through files in folder and load into same table

Vegar — Fri, 18 Oct 2019 12:39:33 GMT

You don't need to use a dummy field. You can even use one or several field that you know exist in the excels. If you do that you won't need to use drop command.

LOAD * [
ExcelCol1, ExcelCol2

]