Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tan_chungkam
Contributor

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.

image.pngSheet1image.pngSheet2image.pngSheet3

1 Solution

Accepted Solutions
Luminary
Luminary

Re: Load multiple sheets from Excel Files

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;
9 Replies
Partner
Partner

Re: Load multiple sheets from Excel Files

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
Contributor

Re: Load multiple sheets from Excel Files

Hi @JordyWegman ,

It does not work.

image.png

Partner
Partner

Re: Load multiple sheets from Excel Files

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
Luminary
Luminary

Re: Load multiple sheets from Excel Files

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;
tan_chungkam
Contributor

Re: Load multiple sheets from Excel Files

@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
Contributor

Re: Load multiple sheets from Excel Files

@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".

 

 

Luminary
Luminary

Re: Load multiple sheets from Excel Files

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.

tan_chungkam
Contributor

Re: Load multiple sheets from Excel Files

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.

Highlighted
Luminary
Luminary

Re: Load multiple sheets from Excel Files

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