Loop through files in folder and load into same table

[arhadisqlik]

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

[Vegar]

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]

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 · Accepted solution]

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]

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]

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

]