topic Problem with "for each" loop in QlikView

Problem with "for each" loop

qw_johan — Tue, 15 Mar 2011 23:23:01 GMT

Hi,

I have run into a problem with my "for each" loop. I found most of my code here.
I am looping through directories and loading any excel files that i find.
The problem is that I start my load one level too deep.

Folder structure:

App (qvw)
+Folder 1
+A
+1A
+1B
+B
+C
+Folder 2
+D
+E
+F

Now I start loading files in folder A .... then B...therefore I miss any excel files that might be directly under "Folder 1", not in subfolder.
I don't want to load the files in Folder 2.

CODE:

for each vDirectory in dirlist (vRoot&'\*')

LET vFolderName = subfield('$(vDirectory)', '', -1);

for each vFile in filelist (vDirectory&'\*.xls')

List:

LOAD *,

'$(vFolderName)' as Folder,

FileDir('$(vDirectory)') as Path,

FileName('$(vFile)') as File

FROM [$(vFile)] (biff, embedded labels, header is 1 lines, table is Blad1$);

next vFile

next vDirectory

Thanks for any help

Problem with "for each" loop

vupen — Wed, 16 Mar 2011 06:07:12 GMT

This is an extract of my VB 6 code. This should work for you:



 Sub ProcessReports(TargetPath As String)
 Dim objFSO' AsScripting.FileSystemObject
 Dim objParFolder' As Folder
 Dim objFolder' As Folder
 Dim objFile' As File
 Set objFSO =CreateObject("Scripting.FileSystemObject")
 Set objParFolder =objFSO.GetFolder(TargetPath)
 For Each objFileIn objParFolder.Files
 If UCase(Right(objFile.Name, 3)) = "XLS" Then
 'File name is objFile.Name
 'Load statement here
 End If
 Next
 'Process subfolders
 For Each objFolder InobjParFolder.SubFolders
 ProcessFilesInSubFolderobjFolder
 Next
 Set objFile = Nothing
 Set objFolder = Nothing
 Set objParFolder = Nothing
 Set objFSO = Nothing
End Sub
Sub ProcessFilesInSubFolder(SubFolder As Folder)
Dim objFile' As File
Dim objFolder' As Folder
For Each objFile In SubFolder.Files
 If UCase(Right(objFile.Name, 3))= "XLS" Then
 'File name is objFile.Name
 'Load statement here
 End If
Next
Set objFile = Nothing
For Each objFolder In SubFolder.SubFolders
 ProcessFilesInSubFolder objFolder
Next
Set objFolder = Nothing
End Sub
Sub cmdProcess_Click()
 ProcessReports vRootPath
End Sub

Problem with "for each" loop

qw_johan — Wed, 16 Mar 2011 13:21:44 GMT

Hi Mulumudi,

Thanks for your reply.
But I feel like my code is better, at least more compact with just ~10 lines of code.
And it works to 99%...

The problem is that my code doesn't scan for excel files at first loop. vRoot holds full path to app directory.
I also tried :
for each vDirectory in dirlist (if(vFlag, vRoot&'\*', vRoot))

Using vRoot i get want at top level, using vRoot&'\*' i get all directories...i need to combine these two options.

Thanks

Problem with "for each" loop

qw_johan — Wed, 16 Mar 2011 19:02:18 GMT

Hi,

I found a solution in the helpfile.

sub DoDir (Root)

for each Ext in 'xls' //'qvw', 'qvo', 'qvs', 'qvt', 'qvd'

for each File in filelist (Root&'\*.' &Ext)

Load '$(File)' as Namn, FileSize( '$(File)' ) as Size, FileTime( '$(Fil)' ) as Filtid

autogenerate 1;

next File

next Ext

for each Dir in dirlist (Root&'\*' )

call DoDir (Dir)

next Dir

end sub

call DoDir ('C:\')