Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tan_chungkam
Creator
Creator

Load multiple sheets from Excel Files

Hi all beloved Qlikers,

I will get monthly Excel reports which contain more than 10 sheets.

Is there anyways to load all sheets in 1 script?

The problem here is there is no header from Sheet 2 onwards.

Please find attached as my sample excel file.

Sheet1Sheet1Sheet2Sheet2Sheet3Sheet3

Labels (2)
1 Solution

Accepted Solutions
Ivan_Bozov
Luminary
Luminary

How about this:

Table:
LOAD
	ID,
	Name,
	Age,
	Gender
FROM [lib://Desktop/TESTING.xlsx]
(ooxml, embedded labels, table is Sheet1);


FOR EACH SheetName IN 'Sheet2', 'Sheet3'
LOAD
	A AS ID,
	B AS Name,
	C AS Age,
	D AS Gender
FROM [lib://Desktop/TESTING.xlsx]
(ooxml, no labels, table is $(SheetName));
NEXT;
vizmind.eu

View solution in original post

10 Replies
JordyWegman
Partner - Master
Partner - Master

Hi Tan,

Do the following:

Table:
Load * Inline [
ID
];

Concatenate(Table)
Load
 *
FROM [lib://Data/My_Excel.xlsx]
(ooxml, embedded labels, table is Sheet*);

 By forcing the concatenate, you will have no problem with having no headers. The * will automatically get all the sheets. You have you change the lib to you own path of course.

Jordy

Climber

Work smarter, not harder
tan_chungkam
Creator
Creator
Author

Hi @JordyWegman ,

It does not work.

image.png

JordyWegman
Partner - Master
Partner - Master

Hi,

Try this with a variable:

 

Table:
Load * Inline [
ID
];

For i = 1 to 10
  vSheet = 'Sheet'& i
  Concatenate(Table)
  Load
   *
  FROM [lib://Data/My_Excel.xlsx]
  (ooxml, embedded labels, table is $(vSheet));
next

 

Jordy

Climber

Work smarter, not harder
Ivan_Bozov
Luminary
Luminary

How about this:

Table:
LOAD
	ID,
	Name,
	Age,
	Gender
FROM [lib://Desktop/TESTING.xlsx]
(ooxml, embedded labels, table is Sheet1);


FOR EACH SheetName IN 'Sheet2', 'Sheet3'
LOAD
	A AS ID,
	B AS Name,
	C AS Age,
	D AS Gender
FROM [lib://Desktop/TESTING.xlsx]
(ooxml, no labels, table is $(SheetName));
NEXT;
vizmind.eu
tan_chungkam
Creator
Creator
Author

@Ivan_Bozov ,

Thank you. It work perfectly.

I have 1 question. 

As it is a monthly report, it could go up to 20 sheets or different sheet number every month.

Is there any script can do that instead of i type in sheet1 and etc.

tan_chungkam
Creator
Creator
Author

@JordyWegman ,

It does not work well here.

As there is no header from Sheet2 onward, thus the outcome is not correct.

image.png

 

And i also have different number of sheets every month. Is it possible to do scripting to load sheet 1 time instead of i changing the  "For i = 1 to 3".

 

 

Ivan_Bozov
Luminary
Luminary

Let's say you have 15 sheets, you can try like this:

Table:
LOAD
	ID,
	Name,
	Age,
	Gender
FROM [lib://Desktop/TESTING.xlsx]
(ooxml, embedded labels, table is Sheet1);


FOR No = 2 TO 15
SheetName = 'Sheet' & No
LOAD
	A AS ID,
	B AS Name,
	C AS Age,
	D AS Gender
FROM [lib://Desktop/TESTING.xlsx]
(ooxml, no labels, table is $(SheetName));
NEXT;

This, of course, requires that your sheets have sequential numbers.

vizmind.eu
tan_chungkam
Creator
Creator
Author

Hi @Ivan_Bozov ,

With this scripting, i would need to change the number for every month because various number of sheets.

Is there anyway to avoid that? 

And also i would lose my previous data if i keep using this script.

Ivan_Bozov
Luminary
Luminary

Easy way:

Put this at the beginning of the script:

 

Set ErrorMode = 0;

 

And change to:

 

FOR No = 2 TO 50 //or any max number you want

 

The script will execute ignoring the errors for the sheets that do not exist. You will get an error message for each sheet that is not there.

 

Another option is to use this method to get the number of sheets:
https://community.qlik.com/t5/QlikView-Documents/Loading-Multiple-Excel-Sheets-Dynamically-along-wit...
https://www.qlikviewaddict.com/2012/03/looping-through-excel-sheets.html

vizmind.eu