topic Loading particular excel sheets into Qlikview in QlikView

Loading particular excel sheets into Qlikview

Fri, 11 May 2012 07:09:14 GMT

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

Loading particular excel sheets into Qlikview

Sat, 12 May 2012 12:31:41 GMT

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

Re: Loading particular excel sheets into Qlikview

Sun, 13 May 2012 22:33:07 GMT

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));

Loading particular excel sheets into Qlikview

Mon, 14 May 2012 08:50:59 GMT

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

Loading particular excel sheets into Qlikview

Mon, 14 May 2012 09:15:59 GMT

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

Loading particular excel sheets into Qlikview

Thu, 24 May 2012 22:10:56 GMT

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