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: 
Anonymous
Not applicable

How to load multiple sheets on excel file into Qlikview

HI,

  I have different sheets in single excel file how i have to load all sheets data .

Please find attached file.

Thanks,

5 Replies
MK_QSL
MVP
MVP

For Each file in FileList('C:\Users\QlikView\Downloads\test.xlsx');

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

Table:

Load *

From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

Drop table tables;

Next file

====================

Define your File Path

For Each file in FileList('C:\Users\QlikView\Downloads\test.xlsx');

alexandros17
Partner - Champion III
Partner - Champion III

The structure of data is Always the same so you could load a sheet into a loop,

Define a string with your months then using the index of a loop with the subfield() expression you can loop through all the sheets, changing the sheet name into the load with a variable containing the piece of substring.

jagan
Luminary Alumni
Luminary Alumni

Hi,

If the sheets are constant then use Concatenate(), if they are dynamic then foreach as Manish specified.

Data:

Load *

From FileName.xlsx (ooxml, embedded labels, table is Sheet1);

Concatenate(Data)

Data:

Load *

From FileName.xlsx (ooxml, embedded labels, table is Sheet2);

Regards,

Jagan.

ajayrai
Partner - Contributor III
Partner - Contributor III

Hi try this script

let vPath='Your file path';

ODBC CONNECT TO $(vPath)

//------ 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;

//EXIT Script;

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

//Get name of each sheet

//Remove all $-signs and quotes '

//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);

Tablename :

Load Statement ;

NEXT

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

//exit script;

Try this first if you still face issue I will provide application and screen shots how to do it.