How to load Excel File with multiple sheets

[eng19872]

How to load Excel File with multiple sheets and each sheet name is different.

Example:

File Name: DataFile.xls

Sheet 1 Name: loream

Sheet 2 Name: excelbu

Sheet 3 Name: Document!°

.

.

.

Sheet n Name: n

[brijendra_singh]

LOAD
*
FROM [lib://Folder/DataFile.xls]
(ooxml, embedded labels, table is loream);

LOAD
*
FROM [lib://Folder/DataFile.xls]
(ooxml, embedded labels, table is excelbu);

LOAD
*
FROM [lib://Folder/DataFile.xls]
(ooxml, embedded labels, table is Document);
.
.
.
.
.
LOAD
*
FROM [lib://Folder/DataFile.xls]
(ooxml, embedded labels, table is n);

Regards

Brijendra

[StarinieriG]

Hi,

try to read this https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-load-all-sheet-in-a-excel-simultaneously-usin...

[Frank_Hartmann]

you could make use of a macro which is called at the beginning of the script. this macro would add a new tab ("SheetNames ") to the excel file with a field called "SheetNames". then the macro would loop through all the exceltabs and adds all the Tab names to the field "SheetNames" and then it saves the excel file.

This would give you the opportunity to load all the tabs at once within a for next loop in script without even knowing how much tabs are present in the excelfile and how they are named!

See attached file

hope this helps

[eng19872]

@Frank_Hartmann 

Please share Macro in seperate text file.

[Frank_Hartmann]

Just realized that my solution is for Qlikview not Sense. In order to get it work in qliksense you will need to outsource the below mentioned vbs code to an external vbs file and then calling the macro via a cmd command in Sense script!!

Following solution is based on Qlikview:

script:

let vSheetNames = Get_Sheet_Names();

1:
LOAD SheetNames
FROM
[getSheetNames.xlsx]
(ooxml, embedded labels, table is SheetNames);


NoConcatenate

Temptable:
Load Concat(Chr(39) & SheetNames & Chr(39),',') as Field_Names
Resident 1; 

Let vFields = Peek('Field_Names',0,'Temptable'); 
DROP Table Temptable;

for each i in $(vFields)

Load *
FROM
[getSheetNames.xlsx]
(ooxml, embedded labels, table is [$(i)]);
next i;

Module:

Function Get_Sheet_Names
set XLApp = CreateObject("Excel.Application")
set XLDOC = XLApp.Workbooks.Open ("C:\Users\Admin\Desktop\getSheetNames.xlsx")   'Please adapt here
XLApp.Visible = False
Set objWorkSheet = XLDOC.Sheets.Add
objWorkSheet.Name = "SheetNames"
XLDOC.Sheets("SheetNames").Select
XLDOC.WorkSheets("SheetNames").Range("A:E").EntireColumn.NumberFormat = "@"
For i = 1 To XLDOC.Sheets.Count
XLApp.Cells(i, 1) = XLDOC.Sheets(i).Name
Next 
XLDOC.save
XLDOC.close
End Function