topic Excel with multiple sheets in QlikView

Excel with multiple sheets

Wed, 05 Jan 2011 10:41:24 GMT

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.

Excel with multiple sheets

prieper — Wed, 05 Jan 2011 11:13:20 GMT

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

Excel with multiple sheets

Wed, 05 Jan 2011 11:19:40 GMT

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

Excel with multiple sheets

Wed, 05 Jan 2011 11:29:31 GMT

Hi,

try this code

for i = 1 to 5

LOAD

Prodname,
ProdDesc
FROM

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

Regards,

Prabhu

Excel with multiple sheets

Wed, 05 Jan 2011 11:59:08 GMT

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 .

Excel with multiple sheets

Wed, 05 Jan 2011 17:25:44 GMT

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.

Excel with multiple sheets

Wed, 05 Jan 2011 17:50:31 GMT

Hi,

try this

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

next

Alex

Excel with multiple sheets

Thu, 06 Jan 2011 07:20:50 GMT

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

Re: Excel with multiple sheets

Tue, 14 Jun 2011 13:14:31 GMT

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

Re: Excel with multiple sheets

Tue, 14 Jun 2011 14:09:34 GMT

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));

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

Re: Excel with multiple sheets

Tue, 14 Jun 2011 14:11:09 GMT

Hi alex,

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 @$(i));

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

Excel with multiple sheets

wizardo — Sat, 09 Jul 2011 09:01:25 GMT

hmm

of course i can be all wrong and your original sheets has 10000, 20000, 30000 rows each totaling in 60000 but just in case....

i think your script is ok.

but you are probably misinterpreting the results you see on your debug ( i assume ofcourse you used it ) window. i assume you ran the script on debug mode with limitation of 10000 rows. what the window is showing is how it read 10000 from the first file. then it read another 10000 from the second file and concatenated them together so in the window you see it as :

MYFILE << sheet$1 10000 rows fetched

MYFILE << sheet$2 20000 rows fetched // that's actuely 10000 rows from the 2nd table added to theos from the 1st

MYFILE << sheet$3 30000 rows fetched // that's actuely 10000 rows from the 3rd table added to theos from the 1st+the 2nd

what it means is that QV read 10000 from each table hence 30000 rows in total

hence when you store it in the QVD file it has "only" 30000

hope this was helpful

Mansyno

Re: Excel with multiple sheets

renjithpl — Fri, 15 Jun 2012 13:22:29 GMT

I have 300 sheets in my excel sheet, i think there is a space constraint for that,

Also i cant use the sheet names in two lines, it takes only in one line. Is there another way of doing this.

I also tried taking 100 sheet first and use the same code below to load next 100. but it doesnt work.

Re: Excel with multiple sheets

renjithpl — Sat, 16 Jun 2012 16:37:41 GMT

What if i have 300 sheets ?

Re: Excel with multiple sheets

barryharmsen — Sat, 16 Jun 2012 20:50:47 GMT

There's also the option to read the metadata from an Excel workbook using OLEDB or ODBC. This lets you enumarate the sheets that are in the workbook using the SQLTABLES statement. You won't have to know how many sheets there are beforehand. (see http://community.qlik.com/message/165200#165200 for an example/

If you combine this with the FOR EACH examples above you should be able to load any number of sheets from Excel.

Re: Excel with multiple sheets

Anonymous — Tue, 11 Sep 2012 10:06:39 GMT

what if the sheet names are different?

ie the tab names are as fallowed sam,kumar...........upto 200 sheets with different names

is there any way to pull all the sheets

Re: Excel with multiple sheets

Anonymous — Tue, 11 Sep 2012 10:06:47 GMT

what if the sheet names are different?

ie the tab names are as fallowed sam,kumar...........upto 200 sheets with different names

is there any way to pull all the sheets

Re: Excel with multiple sheets

Anonymous — Tue, 11 Sep 2012 10:07:09 GMT

what if the sheet names are different?

ie the tab names are as fallowed sam,kumar...........upto 200 sheets with different names

is there any way to pull all the sheets

regards

Harsha

Re: Excel with multiple sheets

Anonymous — Tue, 11 Sep 2012 10:07:38 GMT

what if the sheet names are different?

ie the tab names are as fallowed sam,kumar...........upto 200 sheets with different names

is there any way to pull all the sheets

regards

Harsha

Re: Excel with multiple sheets

Anonymous — Tue, 11 Sep 2012 10:09:34 GMT

what if the sheet names are different?

ie the tab names are as fallowed sam,kumar...........upto 200 sheets with different names

is there any way to pull all the sheets

regards

Harsha