Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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

View solution in original post

22 Replies
prieper
Master II
Master II

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
Author

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
Author

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
Author

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
Author

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
Author

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
Author

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

Not applicable
Author

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
Author

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