topic Re: Load excel data from different sheet in QlikView

Load excel data from different sheet

MindaugasBacius — Fri, 19 Feb 2016 09:12:30 GMT

Hei,

I manage to load information from all excel files in folder by using this script:

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

Now how should I change the script to load information from different sheets in the files?

Thank you!

Re: Load excel data from different sheet

Gysbert_Wassenaar — Fri, 19 Feb 2016 10:42:15 GMT

See this example: Re: Loading Multiple Excel Files and Multiple Excel sheets

Re: Load excel data from different sheet

Chanty4u — Fri, 19 Feb 2016 10:45:35 GMT

chk dis

How to load multiple Excel Files into Qlikview with For Loop

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Re: Load excel data from different sheet

Anonymous — Tue, 06 Sep 2016 01:46:41 GMT

Hi Gysbert,

How do I modify the code you provided in the link when I have no connection nor SQL installed? I tried using the same code and disabling the line about SQL. It loads all .xlsx files with the proper filename but the sheetname cannot be extracted. Thanks!

Re: Load excel data from different sheet

Gysbert_Wassenaar — Tue, 06 Sep 2016 05:41:09 GMT

You don't need to install an SQL database management system. But you do need the odbc driver for excel files. The sqltables command is passed to and execute by the odbc driver to get the list of sheetnames from the excel file.