Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loop For Multiple Excel Worksheets

Hi,

I have an excel workbook with 20 worksheets.  Each worksheet has a different name (i.e. January, February, etc.)  All the worksheets are in the same format and contain the same fields.  How do I create a loop to load all worksheets in the excel workbook in Qlikview?

Thanks

Brian

2 Replies
tresesco
MVP
MVP

LET

vWorkbook ='C:\MultiSheetsLoading.xlsx';



ODBC CONNECT TO [excel;DBQ=C:\MultiSheetsLoading.xlsx];









//------ Create a list of sheets from Excelworkbook

XLtable:        // Name the table that will be created by SQLTables stmt

SQLtables;

DISCONNECT; //Discounnect from ODBC - tables are loaded into document





TRACE Loop through tables;

//A loop is made through all sheets in the documents

FOR i = 0 to NoOfRows('XLtable')-1 //Loop and set number of tables



//Get name of each sheet

//Remove all $-signs and quotes '

LET sheetName = purgechar(PurgeChar(peek('TABLE_NAME', i, 'XLtable'), chr(39)),'$');

TRACE Loading data from $(sheetName);

ExcelLoad:   
//Tablename

LOAD *

FROM MultiSheetsLoading.xlsx (ooxml, embedded labels, table is $(sheetName)); //ooxml for XL2007

NEXT



DROP TABLE XLtable;   //Drop list of possible sheets in XL

This is a sample code. Hope this helps.

Not applicable
Author