topic load from multiple sheets in excel file in QlikView

load from multiple sheets in excel file

Wed, 21 Sep 2011 11:29:31 GMT

HI All,

i have excel file with 21 sheets in it

i tried to load them using this script

FOR Each vSheet in 'Sheet1','Sheet2','Sheet3','Sheet4','Sheet5','Sheet6','Sheet7','Sheet8','Sheet9','Sheet10','Sheet11','Sheet12','Sheet13','Sheet14','Sheet15','Sheet16','Sheet17','Sheet18','Sheet19','Sheet20','Sheet21'

Format:

LOAD Space,

Title,

Report_Metrics,

No,

Metrics_Segment

From [IndustryMetrics\Format.xls](biff,embedded labels,table is $(vSheet));

next;

but this meesage appear

"Can't read from biff file"

So,What's the problem??

Thanks in Advance

load from multiple sheets in excel file

sushil353 — Wed, 21 Sep 2011 11:31:24 GMT

Place your xl file in the same directory/folder where your qlikview application is...

HTH

load from multiple sheets in excel file

Wed, 21 Sep 2011 11:35:10 GMT

i did that

but this error still appear

and another error which is

"Out of virtual and/or logical memory"

load from multiple sheets in excel file

sushil353 — Wed, 21 Sep 2011 11:38:23 GMT

what is your file extension... biff ???

Re: load from multiple sheets in excel file

Wed, 21 Sep 2011 11:41:16 GMT

Hi Mona,

Can you attach a fragment of your Excel file.

Meanwhile, try the following code.

FOR each i = 1 to 21
Format:
LOAD Space,
Title,
Report_Metrics, 
No,
Metrics_Segment
From IndustryMetrics\Format.xls (biff,embedded labels,table is Sheet$(i));
next;

Hope this helps.

Rgrds,

Abhinava

load from multiple sheets in excel file

Wed, 21 Sep 2011 11:41:55 GMT

the file extension is xls

load from multiple sheets in excel file

sushil353 — Wed, 21 Sep 2011 11:43:08 GMT

go to user preferences>export

chek the on BIFF Exports and increase the export memory limit..

Hope now your problem gets resolved 🙂

HTH

Re: load from multiple sheets in excel file

its_anandrjs — Wed, 21 Sep 2011 11:46:38 GMT

Hi,

You want to load data from 1 or more excel so use this

Load

From [IndustryMetrics\*.xls](biff,embedded labels,table is Sheet1));

*.xls

I want to know in all 21 sheets data are in Sheet1 or into other sheets

HTH

Rgds

Anand

load from multiple sheets in excel file

sushil353 — Wed, 21 Sep 2011 11:51:04 GMT

load from multiple sheets in excel file

Wed, 21 Sep 2011 12:15:46 GMT

the error of "Out of virtual and/or logical memory" still occure

after i check on (on biff export) and also increased the export memory limit

load from multiple sheets in excel file

Wed, 21 Sep 2011 12:16:52 GMT

the data in all the 21 sheet

load from multiple sheets in excel file

Wed, 21 Sep 2011 12:55:17 GMT

Any ideas ??!!

load from multiple sheets in excel file

rwunderlich — Wed, 21 Sep 2011 14:30:32 GMT

Try loading one, and then two sheets. Build up slowly so you can see what the problem may be and at what point it breaks.

-Rob

load from multiple sheets in excel file

Wed, 21 Sep 2011 14:52:17 GMT

i tried to load one sheet but the same problem still appear !!

load from multiple sheets in excel file

rwunderlich — Wed, 21 Sep 2011 15:25:56 GMT

Can you open the sheet in Excel?

load from multiple sheets in excel file

Thu, 22 Sep 2011 05:49:07 GMT

Just a thought: should the sheet name not have a '$'-sign behind it?

I.e. 'Sheet1$'

load from multiple sheets in excel file

jonathandienst — Thu, 22 Sep 2011 07:03:27 GMT

I think for a biff load (loading from a file with xls extension) the sheet names need to end in $. Try changing the From statement to

From [IndustryMetrics\Format.xls] (biff, embedded labels, table is [$(vSheet)$]);

Hope that helps

Jonathan

Re: load from multiple sheets in excel file

Thu, 22 Sep 2011 07:31:48 GMT

Hi mona,

Jonathan is right. Wonder how i missed it! lol

use the following code.

FOR each i = 1 to 21
Format:
LOAD Space,
Title,
Report_Metrics, 
No,
Metrics_Segment
From IndustryMetrics\Format.xls (biff,embedded labels,table is Sheet$(i)$);
next;

Rgrds,

Abhinava