topic Re: Loop through subfolders and generate a output file for each in QlikView

Loop through subfolders and generate a output file for each

surendra_masupa — Tue, 01 Sep 2015 11:23:59 GMT

Hi folks,

I've a small requirement to loop through the files in a sub folder and generate a output file for each folder.

As shown above, I need to load all the files under Australia folder , generate a concatenated table and store it into a csv. And it should loop through India, UK, USA folders......

Pls can you suggest a way to achieve this?

Many Thanks in Advance!

Re: Loop through subfolders and generate a output file for each

Clever_Anjos — Tue, 01 Sep 2015 11:32:24 GMT

This should work

for Each dir in DirList('yourpathgoeshere')
  for Each file in FileList('$(dir)\*.csv') // or .txt
      tmp:
      LOAD *
      FROM
      [$(file)](txt, codepage is 1252, no labels, delimiter is ',', msq);
  next;
  store tmp into [$(dir).csv](txt);
  drop table tmp;
next

Re: Loop through subfolders and generate a output file for each

hic — Tue, 01 Sep 2015 11:33:08 GMT

You can use a For-Next loop, e.g.

For each vFileName in Filelist ('C:\Path\*.txt')

Load *,

'$(vFileName)' as FileName

From [$(vFileName)];

Next vFileName

See more on Loops in the Script

HIC

Re: Loop through subfolders and generate a output file for each

Kushal_Chawda — Tue, 01 Sep 2015 11:34:28 GMT

what kind of files you have in folders?. qvd or excels?

Re: Loop through subfolders and generate a output file for each

maxgro — Tue, 01 Sep 2015 11:39:39 GMT

DIRECTORY;

FOR Each Dir in 'Australia', 'India', 'UK', 'USA'

trace Dir=$(Dir);

Concat='';

FOR Each File in filelist ('$(Dir)\*.qvd')

Trace File=$(File);

Table_$(Dir):

$(Concat)

load * from [$(File)] (qvd);

Concat=' concatenate ';

NEXT File;

STORE Table_$(Dir) into Table_$(Dir).csv (txt);

DROP Table Table_$(Dir);

NEXT Dir;

edit: added the bold

Re: Loop through subfolders and generate a output file for each

surendra_masupa — Tue, 01 Sep 2015 11:48:33 GMT

Hi all,

I've to load excel (xlsx) files and I modified the codes sent with xlsx and it doesn't work.

Hi Clever,Henric, Maxximo

The script doesn't generate any table.

Thank for your quick response!

Re: Loop through subfolders and generate a output file for each

Clever_Anjos — Tue, 01 Sep 2015 11:53:32 GMT

Would you mind posting your script here?

Re: Loop through subfolders and generate a output file for each

Kushal_Chawda — Tue, 01 Sep 2015 11:58:49 GMT

Do you have single sheet in excel or multiple sheets?

Re: Loop through subfolders and generate a output file for each

surendra_masupa — Tue, 01 Sep 2015 12:01:38 GMT

for Each dir in DirList('C:\Data Uploads')

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

tmp:

LOAD *

FROM

[$(file)](ooxml, embedded labels, table is [Input file]);

next;

store tmp into [$(dir).csv](txt);

drop table tmp;

Re: Loop through subfolders and generate a output file for each

Clever_Anjos — Tue, 01 Sep 2015 12:02:16 GMT

for Each dir in DirList('C:\Data Uploads\*.*') // Try this

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

tmp:

LOAD *

FROM

[$(file)](ooxml, embedded labels, table is [Input file]);

next;

store tmp into [$(dir).csv](txt);

drop table tmp;

Re: Loop through subfolders and generate a output file for each

surendra_masupa — Tue, 01 Sep 2015 12:02:23 GMT

Multiple sheets, but will be reading only one.

Re: Loop through subfolders and generate a output file for each

Clever_Anjos — Tue, 01 Sep 2015 12:06:58 GMT

If possible, please enable log file (Document Properties / General / Generate logfile) and post it here too

Re: Loop through subfolders and generate a output file for each

surendra_masupa — Tue, 01 Sep 2015 12:09:06 GMT

Hi Clever,

It works. But it throws an error for the folders which don't contain any file. Is there a way we can handler this?

Many thanks for your help.

Re: Loop through subfolders and generate a output file for each

Clever_Anjos — Tue, 01 Sep 2015 12:45:44 GMT

It´s pretty simple

for Each dir in DirList('yourpathgoeshere')
for Each file in FileList('$(dir)\*.csv') // or .txt
if filesize('$(file)') > 0 then
tmp:
LOAD *
FROM
[$(file)](txt, codepage is 1252, no labels, delimiter is ',', msq);
end if
next;
store tmp into [$(dir).csv](txt);
drop table tmp;
next

Re: Loop through subfolders and generate a output file for each

surendra_masupa — Tue, 01 Sep 2015 13:22:00 GMT

Hi Clever

Sorry, It didn't work. It's still unable to store the tmp table for the folder which don't have any files.

I tried to set ErrorMode=0 and i works, but don't think this is the best way to achieve this.

Thank you!

Re: Loop through subfolders and generate a output file for each

Clever_Anjos — Tue, 01 Sep 2015 14:00:22 GMT

Would you mind posting the log file here?

Let me check

Re: Loop through subfolders and generate a output file for each

paulo_pinto — Thu, 19 Sep 2019 19:33:32 GMT

Had some issues trying to run this solution because line 1.

It did not seem clear to me. I think it is a bit better to understand

1.for Each dir in DirList('yourpathgoeshere\*')