Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Excel with multiple sheets

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.

1 Solution

Accepted Solutions
Not applicable

Excel with multiple sheets

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

22 Replies
prieper
Honored Contributor II

Excel with multiple sheets

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

HTH
Peter

Not applicable

Excel with multiple sheets

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

Not applicable

Excel with multiple sheets

Hi,

try this code

for i = 1 to 5

LOAD

Prodname,
ProdDesc
FROM

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

next

Regards,

Prabhu

Not applicable

Excel with multiple sheets

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 .









Not applicable

Excel with multiple sheets

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

Not applicable

Excel with multiple sheets

Hi,

try this

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

next

Alex

Not applicable

Excel with multiple sheets

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

Not applicable

Re: Excel with multiple sheets

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

Not applicable

Re: Excel with multiple sheets

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