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.

[#2]

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

          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

[wizardo]

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

[renjithpl]

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.

[renjithpl]

What if i have 300 sheets ?

[barryharmsen]

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.

[Anonymous]

Hi

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

[Anonymous]

Hi

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

[Anonymous]

Hi

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

[Anonymous]

Hi

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

[Anonymous]

Hi

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