topic Re: Load all sheets at a time in QlikView

Load all sheets at a time

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

I have a excel sheet ABC.xlsx which contains 5 sheets : Sheet1,Seet2,Sheet3,Sheet4,Sheet5

I want to load all these 5 sheets at a time in qlikview

please tell me how to do it?

Thanks

Re: Load all sheets at a time

its_anandrjs — Thu, 14 Sep 2017 13:07:56 GMT

Use ODBC for this excel loading.

Re: Load all sheets at a time

Clever_Anjos — Thu, 14 Sep 2017 13:08:45 GMT

Basically you can´t to it at one time but you can

For i = 1 to 5

load * from excel.xlsx(table is Sheet$(i)

Re: Load all sheets at a time

its_anandrjs — Thu, 14 Sep 2017 13:09:44 GMT

Check this For loop for loading the data from all sheet.

FOR index = 0 to NoOfRows('SheetNames')-1 // Loops for each sheet in the Excel file.

LET sheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));

TableName:

Load * ,

FileBaseName()as FIle,

FileDir() as Dir,

FileName() as File_Name,

'$(sheetName)' as Sheet_name

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

NEXT index

DROP TABLE SheetNames;

Check this thread as well:_

Load all Excel files and all sheets in a folder

Re: Load all sheets at a time

MindaugasBacius — Thu, 14 Sep 2017 13:10:37 GMT

I recommend to study the code in the link as the scenario seems the same:

Load Multiple excel sheets using For loop

Re: Load all sheets at a time

Anonymous — Thu, 14 Sep 2017 13:11:07 GMT

can you mention the steps for this

Re: Load all sheets at a time

its_anandrjs — Thu, 14 Sep 2017 13:15:30 GMT

Check this attached thread please you can do this with out ODBC also. For ODBC connection go to the ODBC connection manager and find out the connector to install the excel ODBC.

Load all Excel files and all sheets in a folder