Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
arhadisqlik
Contributor II
Contributor II

Loop through files in folder and load into same table

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

 

 

 

 

 

 

 

 

1 Solution

Accepted Solutions
Vegar
MVP
MVP

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

 

View solution in original post

5 Replies
Vegar
MVP
MVP

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 ;

arhadisqlik
Contributor II
Contributor II
Author

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

 

 

Vegar
MVP
MVP

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

 

arhadisqlik
Contributor II
Contributor II
Author

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

Vegar
MVP
MVP

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

]