Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

loop through to load all files from a folder and its subfolders?

Hi all,

This is my folder structure

MainFolder

       SubFolderr1

                      File1

                      File2

       SubFolder2

                      File3

                      File4

       SubFolder3

                      File5

                      File6

How to load all the files dynamically if i give only "MainFolder" Name.

Thanks in advance

Regards

Kumar

1 Solution

Accepted Solutions

Re: loop through to load all files from a folder and its subfolders?

Then you need to set the header size correctly when you create your Load statement:

File wizard.jpg

This Load works fine on my computer:

Set vConcatenate = ;

sub ScanFolder(Root)

          for each FileExtension in 'csv'

                    for each FoundFile in filelist( Root & '\*.' & FileExtension)

                              FileList:

                              $(vConcatenate)

                              LOAD *, '$(FoundFile)' as SourceFile

                              FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

                              Set vConcatenate = Concatenate;

                    next FoundFile

          next FileExtension

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

                    call ScanFolder(SubDirectory)

          next SubDirectory

end sub

Call ScanFolder('C:\Users\hic\Documents\2012\Work\QV Apps\DoDir') ;

HIC

27 Replies

Re: loop through to load all files from a folder and its subfolders?

The basic structure would be the following, but you can add more in the Load statement.

HIC

sub ScanFolder(Root)

          for each FileExtension in 'qvw','mp3','ape','flac','ogg'

                    for each FoundFile in filelist( Root & '\*.' & FileExtension)

                              FileList:

                              Load

                                        '$(FoundFile)'                     as [FilenameWithPath],

                                        '$(FileExtension)'           as [Extension]

                              Autogenerate(1);

                    next FoundFile

          next FileExtension

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

                    call ScanFolder(SubDirectory)

          next SubDirectory

end sub

Call ScanFolder('.') ;

Not applicable

Re: loop through to load all files from a folder and its subfolders?

Hi Henric,

This is what i am using

sub ScanFolder(Root)

          for each FileExtension in 'csv'

                    for each FoundFile in filelist( Root & '\*.' & Extension)

                              FileList:

                              Load

                                        '$(FoundFile)'                     as [FilenameWithPath],

                                        '$(FileExtension)'           as [Extension]

                              Autogenerate(1);

                    next FoundFile

          next FileExtension

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

                    call ScanFolder(SubDirectory)

          next SubDirectory

end sub

Call ScanFolder('E:\ManFolder') ;

It is reading MainFolder and its SubFoldedrs.

But not loading the data.

Regards

Kumar

Re: loop through to load all files from a folder and its subfolders?

Then you should replace the load statement by a

Load * from [$(FoundFile)] ( ... file specification ...)

(You need to generate one Load statement the normal way to get the file specification right.)

Do all the files have identical structure? if not, you should instead use the following so that you make sure that all files are concatenated:

Set vConcatenate = ;

sub ScanFolder(Root)

          for each FileExtension in 'csv'

                    for each FoundFile in filelist( Root & '\*.' & Extension)

                              FileList:

                              $(vConcatenate)

                              Load * from [$(FoundFile)] ( ... file specification ...) ;

                              Set vConcatenate = Concatenate;

                    next FoundFile

          next FileExtension

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

                    call ScanFolder(SubDirectory)

          next SubDirectory

end sub

Call ScanFolder('E:\ManFolder') ;

HIC

Not applicable

Re: loop through to load all files from a folder and its subfolders?

Hi Henric,

It's not working.

Please collect the sample structure along with data in the attachment.

My target is to prepare a single qvd for all those files and those are having the same column structure.

(Note:read the files from 3rd row in csv files)

Regards

Kumar

Re: loop through to load all files from a folder and its subfolders?

Then you need to set the header size correctly when you create your Load statement:

File wizard.jpg

This Load works fine on my computer:

Set vConcatenate = ;

sub ScanFolder(Root)

          for each FileExtension in 'csv'

                    for each FoundFile in filelist( Root & '\*.' & FileExtension)

                              FileList:

                              $(vConcatenate)

                              LOAD *, '$(FoundFile)' as SourceFile

                              FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

                              Set vConcatenate = Concatenate;

                    next FoundFile

          next FileExtension

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

                    call ScanFolder(SubDirectory)

          next SubDirectory

end sub

Call ScanFolder('C:\Users\hic\Documents\2012\Work\QV Apps\DoDir') ;

HIC

Employee
Employee

Re: loop through to load all files from a folder and its subfolders?

Awesome Henric! Thanks for sharing!

Tyler

Not applicable

Re: loop through to load all files from a folder and its subfolders?

i am getting syntax error in

$(vConcatenate).

please help me how to reslove this

Re: loop through to load all files from a folder and its subfolders?

You will get a swiggly line under the $(vConcatenate) since the syntax check does not know what text this dollar-expansion will create. But this is not a problem. The script will run anyway.

HICSyntax error.png

rustyfishbones
Honored Contributor II

Re: loop through to load all files from a folder and its subfolders?

Hi Henric,

Is it possible to Parametrize the FileExtension

so that the CALL statement would be

CALL ScanFolder('csv','C:\Users\hic\Documents\2012\Work\QV Apps\DoDir')

Regards

Alan