topic Load excel files from multiple folders in QlikView

Load excel files from multiple folders

MindaugasBacius — Thu, 21 Jan 2016 08:47:43 GMT

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,

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!

Re: Load excel files from multiple folders

sunny_talwar — Thu, 21 Jan 2016 10:39:05 GMT

Had a similar question, see if this helps:

Re: Count of records

Re: Load excel files from multiple folders

tamilarasu — Thu, 21 Jan 2016 11:29:54 GMT

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,

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

tamilarasu — Thu, 21 Jan 2016 11:44:24 GMT

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

Re: Load excel files from multiple folders

MindaugasBacius — Thu, 21 Jan 2016 12:20:17 GMT

Tamil,

Both of your scripts work!

Thank you a lot.