Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

MindaugasBacius
Valued Contributor II

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!

Tags (2)
1 Solution

Accepted Solutions

Re: Load excel files from multiple folders

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')

4 Replies

Re: Load excel files from multiple folders

Had a similar question, see if this helps:

Re: Count of records

Re: Load excel files from multiple folders

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

Re: Load excel files from multiple folders

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
Valued Contributor II

Re: Load excel files from multiple folders

Tamil,

Both of your scripts work!

Thank you a lot.

Community Browser