Excel with multiple sheets

[#1]

Hi,

How to load the data from the multiple sheets of an excelsheet. I have an excel which has 5 sheets all the data is from single table.

Do i need to write a seperate load statement for each sheet and concatenate that all. Is there any way we can do that in a single script.

[prieper]

There had been several solutions for this in the forum, there is a site in German: gidf.de
One of the most recent threads is probably: http://community.qlik.com/forums/p/32247/124633.aspx

HTH
Peter

[#3]

Hello,

you can use a for each with a list of your sheetnames in a variable like the following and load the whole data into one QV-Table:

for each vSheet in 'sheet1', 'sheet2', 'sheet3'
LOAD id,
 category,
 number,
 costs,
 O
FROM
[lokal QV-Forum\TESTweights.xlsx]
(ooxml, embedded labels, table is $(vSheet));
NEXT;

Regards, Roland

[#4]

Hi,

try this code

for i = 1 to 5

LOAD

Prodname,
ProdDesc
FROM

(ooxml, embedded labels, table is Sheet$(i));

next

Regards,

Prabhu

[#5]

Hi Roland and prabhu,

Thanks for your response.I tried both of your solution It is not working and i am getting error. Every sheet is named in my excel.I am using excel 97-2003 format. Please find the script i am have

Load ........From (biff,embedded labels,table is Feb$). I have noticed the difference in the from statement .

[#6 · Accepted solution]

Hello XXX,

lets say that your sheet- (or table-)names are Jan$, Feb$ and Oct$ (all in the same file "T6.xls"). Then you can use a "for each .... next" like this:

for each vSheet in 'Jan$', 'Feb$','Oct$'
MyTableName:
Load ........From (biff,embedded labels,table is $(vSheet));
next;

Note: not syntax checked.

RR

[#7]

Hi,

try this

for i=1 to 5
tab1:
LOAD column1,
column2,
column4
FROM
TEST1.xls
(biff, embedded labels, table is @$(i));

next

Alex

[#8]

Thanks Alex but still i am geting error when using ur script cannot find the table in Biff

[#9]

Hi XXX,

i got same error in my file also . I tried this process, pls if you find the solution for this . Pls post me a message how to work on it.

Thnks

[#10]

Hi Prabhu,

I tried your method for Excel fetching the lines from different sheets of same Excel file.

BUT WHEN I AM CREATING A QVD FOR THIS, IT IS CREATING A QVD FOR LAST SHEET i.e; it is not adding all the sheets in excel file.

I.e; my sheet1 fetches 10000 lines, sheet2 fetches 20000 lines and sheet3 fetches 30000 lines.

After i am applying the QVD to my file it is only fetching Sheet3 lines(i.e; 30000 lines). BUt i need to have total 60000 lines from excel file.

PLS see below example what i had done in my file.

1.)      for i = 1 to 3

          MYFILE:

          LOAD

          Prodname,
          ProdDesc
          FROM
         
          (ooxml, embedded labels, table is Sheet$(i));

          next

          Store MYFILE into test123.qvd;

2.)     File:

          Load Prodname,

                   ProdDesc,

          From test123.qvd;

Is is there any modifications required to get my requirement

If you are having any doubts pls let me know.

Thnks