Loading particular excel sheets into Qlikview

[#1]

Hello, I am a total newbie, I am learning QlikView.

I just have questions when it comes to loading data in Qlikview (I can't find this information in the tutorial documentation)

I have excel file with multiple worksheets, for simplicity, lets call them Sheet1, Sheet2, Sheet3

I am trying to load the files into Qlikview (I have multiple excel files, but they are all in similar format)

Currently, i just load them all using myfolder/*.xls

the questions I have:

1. Does Qlikview automatically load all data from each sheet in all my excel files?

2. How do I specify, for example, if I just wanted to load Sheet1 and Sheet2? or just Sheet1?

Thank you.

Aldo

[#2]

Hi Aldo,

1. no QV does not load all excel - sheets automatically.

2a. You can define a variable (for ex. implicit in a loop) similar to this:

for i = 1 to 3

LOAD

. . .

(ooxml, embedded labels, table is Sheet$(i));  // the $() is for using the variable

NEXT i

;

2b. sth like this:

for Each vSheet in 'Tabelle1', 'Tabelle2'

LOAD

. . .

(ooxml, embedded labels, table is $(vSheet));

NEXT vSheet

;

HtH

Roland

[#3]

Hello Roland, thank you for your reply

I made test file, 2 excel files in a folder, each containing Sheet1 and Sheet2.

I tried to load them using the following but I got error (Unknown file format specifier:table is Sheet$(1))

all I tried to do is only to load data from Sheet1 from both excel files

Did i do something wrong?

LOAD

Date,
Month,
Customer ID
FROM
[Data\Traffic\*.xlsx]
(ooxml, embedded labels, table is Sheet$(1));

[#4]

ok,

I will try to prezice your exam:

use a LOOP like this, as I wrote in my post under 2b)

for Each vSheet in 'Sheet1', 'Sheet2'  // now we defined a variable called vSheet

LOAD

Date,
Month,
Customer ID
FROM
[Data\Traffic\*.xlsx]
(ooxml, embedded labels, table is $( vSheet));  // here we use the contents of vSheet via $()-Operator

NEXT vSheet ;  // looping

HtH

Roland

[#5]

Hi Aldo,

did recognize your star '*' in the filename too late. For this you will need a second loop. Outer loop is going for every file and inner loop is going for every specified table. Check your manual for using (contents of) variables via $()-Operator.

Examinate this code snippet:

for each vFile in filelist ('Data\Traffic\*.xlsx');  // outer (first) loop

FOR Each vSheet in 'Sheet1', 'Sheet2'  // inner loop

LOAD A

FROM  $(vFile)  // containts filename and path, one per loop(vFile)

(ooxml, embedded labels, table is $(vSheet));

NEXT vSheet;

NEXT vFile;

Regards

Roland

[#6]

Hello Roland, thank you very much for your reply

turns out different excel format (pre-2007) can cause problem in some syntaxes, at least in the qlikview version installed in my desktop

but generally, your advice was helpful 🙂

for example, i played around with the sheet name and change it into "sheet one"

and when i loaded it, i had to use (ooxml, embedded labels, table is [sheet one]);

when i just wanted to load that sheet one table

hope that helps anyone else too