load from multiple sheets in excel file

[#1]

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

[#2]

the data in all the 21 sheet

[#3]

Any ideas ??!!

[rwunderlich]

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

[#5]

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

[rwunderlich]

Can you open the sheet in Excel?

[#7]

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

I.e. 'Sheet1$'

[jonathandienst]

Hi

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

[#9]

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