Load multiple sheets from Excel Files

[tan_chungkam]

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.

Sheet1Sheet2Sheet3

[JordyWegman]

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

[tan_chungkam]

Hi @JordyWegman ,

It does not work.

[JordyWegman]

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

[Ivan_Bozov · Accepted solution]

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]

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

@JordyWegman ,

It does not work well here.

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

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]

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]

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]

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