Load excel files from multiple folders

[MindaugasBacius]

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!

[sunny_talwar]

Had a similar question, see if this helps:

Re: Count of records

[tamilarasu]

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

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]

Tamil,

Both of your scripts work!

Thank you a lot.