Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Sheet1
Sheet2
Sheet3
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;
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
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
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;
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.
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".
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.
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.
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