Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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

View solution in original post

29 Replies
Highlighted

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('.') ;

Highlighted
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

Highlighted

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

Highlighted
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

Highlighted

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

View solution in original post

Highlighted
Employee
Employee

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

Awesome Henric! Thanks for sharing!

Tyler

Highlighted
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

Highlighted

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

Highlighted
Master II
Master 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