How to load multiple sheets on excel file into Qlikview

[Anonymous]

HI,

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

Please find attached file.

Thanks,

[MK_QSL]

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]

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.

[avinashelite]

check this thread

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

[jagan]

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]

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.