Loop For Multiple Excel Worksheets

Wed, 19 Jun 2013 03:17:45 GMT

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

Re: Loop For Multiple Excel Worksheets

tresB — Wed, 19 Jun 2013 04:17:55 GMT

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.

Re: Loop For Multiple Excel Worksheets

Wed, 19 Jun 2013 05:12:27 GMT

see link

http://community.qlik.com/thread/54342

topic Re: Loop For Multiple Excel Worksheets in QlikView

Loop For Multiple Excel Worksheets

Re: Loop For Multiple Excel Worksheets

Re: Loop For Multiple Excel Worksheets