Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

How to load Excel File with multiple sheets

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

 

5 Replies
Highlighted
Contributor
Contributor

Re: How to load Excel File with multiple sheets

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

Highlighted
Partner
Partner

Re: How to load Excel File with multiple sheets

Highlighted
Master II
Master II

Re: How to load Excel File with multiple sheets

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

 

Highlighted
Creator
Creator

Re: How to load Excel File with multiple sheets

@Frank_Hartmann 

Please share Macro in seperate text file.

Highlighted
Master II
Master II

Re: How to load Excel File with multiple sheets

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