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

[#1]

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

[hic]

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

[#3]

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]

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

[#5]

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 · Accepted solution]

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

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]

Awesome Henric! Thanks for sharing!

Tyler

[#8]

i am getting syntax error in

$(vConcatenate).

please help me how to reslove this

[hic]

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.

HIC

[rustyfishbones]

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