Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MindaugasBacius
Partner
Partner

Load excel files from multiple folders

Hello,

I am facing a problem while trying to load information from excel files from different folders.

I have also checked:

Loading data from multiple xls-files from folder with subfolder

Load files from multiple folders within a direc... | Qlik Community

https://community.qlik.com/message/262748?_ga=1.115812033.61625967.1447666596#262748

Now I am troubleshooting scripts.

Trying two options.

The first:

Let vDirectory = 'C:\Users\Mindaugasb\Desktop\Orders\2016';

Sub DoDir ($(vDirectory))

FOR Each SUBDIR in $(vDirectory)

FOR Each File in FileList ($(SUBDIR) & '\*.xls')

Orders:

LOAD @1,

     @2

FROM

$(File)

(biff, no labels, table is [Sheet1$]);

NEXT File

For this I get a bunch of errors.

The second:

Set vConcatenate = ;

sub ScanFolder(Root)

          for each FileExtension in 'xls'

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

                              FileList:

                              $(vConcatenate)

                              LOAD  @1,

                                    @2

                              from [$(FoundFile)]  (biff, no labels, table is [Sheet1$];

                              Set vConcatenate = Concatenate;

                    next FoundFile

          next FileExtension

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

                    call ScanFolder(SubDirectory)

          next SubDirectory

end sub

Call ScanFolder('C:\Users\Mindaugasb\Desktop\Orders\2016') ;

For this one no errors but nothing loads.

Actually I do not know which of the scripts needs to be corrected but the core problem is I do not know how to load excel files from different folders. Could anyone please help me out with this problem?

Thank you!

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

I tried the first one as well but modified like below.

SUB DoDir(Root)

FOR each File in filelist( Root & '\*.xls')

    LOAD @1, 

         @2

    FROM

    [$(File)]

    (biff, no labels, table is Sheet1$);

NEXT File

FOR each Dir in Dirlist (Root&'\*')

     CALL DoDir(Dir)

NEXT Dir

END SUB

CALL DoDir('C:\Users\Mindaugasb\Desktop\Orders\2016')

View solution in original post

4 Replies
sunny_talwar

Had a similar question, see if this helps:

Re: Count of records

tamilarasu
Champion
Champion

Hi Bacius,

I just took your second script and corrected a line. Check and let me know.

Set vConcatenate = ;

sub ScanFolder(Root)

          for each FileExtension in 'xls'

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

                              FileList:

                              $(vConcatenate)

                              LOAD  @1, 

                                    @2

                              from [$(FoundFile)]  (biff, no labels, table is [Sheet1$];

                              Set vConcatenate = Concatenate;

                    next FoundFile

          next FileExtension

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

                    call ScanFolder(SubDirectory)

          next SubDirectory

end sub

Call ScanFolder('C:\Users\Mindaugasb\Desktop\Orders\2016') ;

tamilarasu
Champion
Champion

I tried the first one as well but modified like below.

SUB DoDir(Root)

FOR each File in filelist( Root & '\*.xls')

    LOAD @1, 

         @2

    FROM

    [$(File)]

    (biff, no labels, table is Sheet1$);

NEXT File

FOR each Dir in Dirlist (Root&'\*')

     CALL DoDir(Dir)

NEXT Dir

END SUB

CALL DoDir('C:\Users\Mindaugasb\Desktop\Orders\2016')

MindaugasBacius
Partner
Partner
Author

Tamil,

Both of your scripts work!

Thank you a lot.