topic Re: How to load multiple sheets on excel file into Qlikview in QlikView

How to load multiple sheets on excel file into Qlikview

Anonymous — Thu, 09 Jul 2015 08:50:23 GMT

HI,

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

Please find attached file.

Thanks,

Re: How to load multiple sheets on excel file into Qlikview

MK_QSL — Thu, 09 Jul 2015 08:58:16 GMT

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

Re: How to load multiple sheets on excel file into Qlikview

alexandros17 — Thu, 09 Jul 2015 08:59:43 GMT

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.

Re: How to load multiple sheets on excel file into Qlikview

avinashelite — Thu, 09 Jul 2015 09:28:44 GMT

check this thread

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

Re: How to load multiple sheets on excel file into Qlikview

jagan — Thu, 09 Jul 2015 09:39:41 GMT

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.

Re: How to load multiple sheets on excel file into Qlikview

ajayrai — Tue, 02 Feb 2016 17:18:28 GMT

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 ;

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.