Question About Loop (Foreach)

[pgalvezt]

Hello!!

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

1. for Each dir in DirList('yourpathgoeshere')
2.   for Each file in FileList('$(dir)\*.csv') // or .txt (What I should to put here in '$(dir)\)
3.    if filesize('$(file)') > 0 then
4.       tmp:
5.       LOAD *
6.       FROM
7.       [$(file)](txt, codepage is 1252, no labels, delimiter is ',', msq);
8.    end if
9.   next;
10.   store tmp into [$(dir).csv](txt);
11.   drop table tmp;
12. 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

  Next

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

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

Thanks!

[maxgro]

try

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

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

          trace $(file);

[pgalvezt]

Hello!,

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

[maxgro]

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

NEXT

[pgalvezt]

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!

[tamilarasu · Accepted solution]

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.

[pgalvezt]

Finally, I could link all my script to this.

Ty