topic Re: Question About Loop (Foreach) in QlikView

Question About Loop (Foreach)

pgalvezt — Thu, 31 Mar 2016 20:45:51 GMT

Hello!!

Im trying to load diferents xls files into subfolders into a main folder and I Try to follow this:

for Each dir in DirList('yourpathgoeshere')
for Each file in FileList('$(dir)\*.csv') // or .txt (What I should to put here in '$(dir)\)
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

In my script i have this:

for Each dir in DirList('C:\Users\pgalvez\Desktop\QLIKVIEW 2016\')

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

// if filesize('$(file)') > 0 then

temp:

LOAD *

FROM

[Root]

(biff, embedded labels, table is GENERAL$, filters(

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 5)),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

//end if

//STORE tempINTO '$(dir)\*.xls'.qvd(qvd);

But Doesn't work, QlikView read nothing Pleas help!

Thanks!

Re: Question About Loop (Foreach)

maxgro — Thu, 31 Mar 2016 21:18:46 GMT

try

for Each dir in DirList('C:\Users\pgalvez\Desktop\QLIKVIEW 2016') \\ remove last \

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

trace $(file);

Re: Question About Loop (Foreach)

pgalvezt — Thu, 31 Mar 2016 21:22:14 GMT

Hello!,

Thanks for your replying but doesn't work, QlikView show me nothing...

Re: Question About Loop (Foreach)

maxgro — Thu, 31 Mar 2016 21:35:56 GMT

do you have files in the folder?

this works (I get a list of files in the script execution progress) on my Qlik

for Each dir in DirList('C:\Users\mgrossi\Downloads')

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

trace $(file);

NEXT

Re: Question About Loop (Foreach)

pgalvezt — Mon, 04 Apr 2016 21:06:08 GMT

Hi,

Still Doesn't work, I attached a simple path, could you pls put your example in the path, If the script works for you i guess should works for me. Thanks!

Re: Question About Loop (Foreach)

tamilarasu — Tue, 05 Apr 2016 01:49:38 GMT

Hi,

Check the below script,

Sub ScanFolder(Root) 
    For Each FileExtension in 'xlsx' 
       For Each FoundFile in Filelist( Root & '\*.' & FileExtension) 

         Data: 
           Load  A as FieldValues,
           Subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1) as FileName
           FROM [$(FoundFile)] (ooxml, no labels, table is [Hoja1]);
                      
        Next FoundFile 
     Next FileExtension 
       
     For Each SubDirectory in Dirlist( Root & '\*' ) 
        Call ScanFolder(SubDirectory) 
     Next SubDirectory 
End Sub 

Call ScanFolder('C:\Users\Tamil\Desktop\Prueba Buscar Archivos') ;  // change your path accordingly

Sample QVW file attached against your sample data.

Re: Question About Loop (Foreach)

pgalvezt — Tue, 05 Apr 2016 13:56:41 GMT

Finally, I could link all my script to this.