Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
tan_chungkam
Creator
Creator

Load vary number of sheets from Excel Files

Hi all lovely people,

I always have a monthly Excel File (XLS format) with different number of sheets in every month.

At the moment, i need to change certain in order to absorb the difference of sheet numbers.

Not only that, the previous loaded Excel file will not always be kept after i have a new monthly excel file in the qliksense.

Hence i am looking a way to solve above both issues (Load the file no matter the number of sheets & Keep the previous loaded file).

Please find screenshot my scripting and my excel file sturcuture.

image.pngSheet1Sheet1sme structure from Sheet2 onwardsme structure from Sheet2 onward

 

Labels (2)
1 Reply
Frank_Hartmann
Master II
Master II

try like this:

Table:
LOAD [Main Order],
Order,
OT,
Reference,
Article,
Type,
[invoice mark],
qty_ordered,
Sales_Price_in_MYR,
Incurred_Costs,
curr,
[curr rate],
per
FROM
[C:\Users\admin\Desktop\Mappe1.xlsx]
(ooxml, embedded labels, table is Sheet1);

 

For i = 2 to 1000 //choose high number according the number of your sheets
vSheet = 'Sheet'& i
Concatenate(Table)
Load
A as [Main Order],
B as Order,
C as OT,
D as Reference,
E as Article,
F as Type,
G as [invoice mark],
H as qty_ordered,
I as Sales_Price_in_MYR,
J as Incurred_Costs,
K as curr,
L as [curr rate],
M as per
FROM [C:\Users\admin\Desktop\Mappe1.xlsx]
(ooxml, no labels, table is $(vSheet));

next