topic Re: EXCEL SHEETS in QlikView

EXCEL SHEETS

upaliwije — Sat, 14 Nov 2015 13:17:40 GMT

I have in my excel work book several sheets to be loaded to QV documents and with following script it is loaded correctly

FOR EACH vsheet IN 'MV','CI','FF','OE','PM'

CrossTable(MONTH, AMOUNT, 5)

LOAD BCOD,

CC,

ITEM AS O_ITEM,

[Description of Item] AS ITEM,

TYPE,

Jan,

Feb,

Mar

FROM

[ALL CAPITAL.xlsx]

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

This workbook is from office 2007 (xlsx) but when I try do the same thing with office 2003 (xls) it is giving error message. IS it not possible to this with excel with extension .xls pls hlep

Re: EXCEL SHEETS

sunny_talwar — Sat, 14 Nov 2015 13:29:38 GMT

May be this change might help:

From

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

(biff, embedded labels, table is $(vsheet));

Re: EXCEL SHEETS

settu_periasamy — Sat, 14 Nov 2015 13:33:06 GMT

ooxml - for xlsx files.

biff - for xls files

Load

...

FROM

[Source..xls]

(biff, no labels, table is Sheet1$);

Re: EXCEL SHEETS

settu_periasamy — Sat, 14 Nov 2015 13:35:58 GMT

Informative discussion..

biff Vs. ooxml

Re: EXCEL SHEETS

upaliwije — Sat, 14 Nov 2015 13:47:34 GMT

Thanks

I tried that then I get this message

Cannot locate table in BIFF file

LOAD BCOD,

[Branch Name],

CC,

ITEM,

[Description of Item],

TYPE,

Value,

Jan,

Feb,

Mar,

FROM

[ALL CAPITAL.xls]

(biff, embedded labels, table is MV)

Re: EXCEL SHEETS

settu_periasamy — Sat, 14 Nov 2015 13:53:00 GMT

Hi,

Just to make sure, Do you have the fields after 'Mar' or is just sample?

remove the comma before FROM field and end of the script with semicolon..

Load

...

Mar

FROM

[ALL CAPITAL.xls]

(biff, embedded labels, table is MV$);

Edit

forgot to mention. should use '$' Like

table is MV$

Re: EXCEL SHEETS

tamilarasu — Sat, 14 Nov 2015 13:53:26 GMT

Hi Upali,

Did you rename the file from xlsx to xls directly.? If so, open your original xlsx file and use save as optition to store it as .xls (Excel 97-2003).

Re: EXCEL SHEETS

upaliwije — Sat, 14 Nov 2015 13:56:47 GMT

YES

Re: EXCEL SHEETS

tamilarasu — Sat, 14 Nov 2015 13:58:02 GMT

See my above comment and try again. If you rename the file manually, you will get this error. After you save the file as I said, try the script again. Also don't forget to change it as biff.

Re: EXCEL SHEETS

upaliwije — Sat, 14 Nov 2015 13:58:18 GMT

Sorry . This is only a sample

Re: EXCEL SHEETS

settu_periasamy — Sat, 14 Nov 2015 13:59:54 GMT

Did you check my edited comment.. (Above)

Re: EXCEL SHEETS

upaliwije — Sat, 14 Nov 2015 14:12:03 GMT

Yes I did all you suggested. I attach h/w my sample data and pls have a look and advise me

Re: EXCEL SHEETS

settu_periasamy — Sat, 14 Nov 2015 14:20:10 GMT

As i already told.. you should give '$' in the table name like..

(biff, embedded labels, table is $(vsheet)$);

Re: EXCEL SHEETS

sunny_talwar — Sat, 14 Nov 2015 14:26:48 GMT

Yup either that or this:

FOR EACH vsheet IN 'MV$','CI$','FF$'

CrossTable(MONTH, AMOUNT, 3)

LOAD BCOD,

[Description of Item],

TYPE,

Jan,

Feb,

Mar,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec

FROM

test.xls

(biff, embedded labels, table is $(vsheet));

NEXT