Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
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
hic
Former Employee
Former Employee

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
hic
Former Employee
Former Employee

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
Author

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

hic
Former Employee
Former Employee

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
Author

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

hic
Former Employee
Former Employee

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

Tyler_Waterfall
Employee
Employee

Awesome Henric! Thanks for sharing!

Tyler

Not applicable
Author

i am getting syntax error in

$(vConcatenate).

please help me how to reslove this

hic
Former Employee
Former Employee

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

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