4 Replies Latest reply: Jan 21, 2016 7:20 AM by Mindaugas Bacius RSS

    Load excel files from multiple folders

    Mindaugas Bacius

      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!

        • Re: Load excel files from multiple folders
          Sunny Talwar

          Had a similar question, see if this helps:

           

          Re: Count of records

          • Re: Load excel files from multiple folders
            Tamil Nagaraj

            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
              Tamil Nagaraj

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