Best way to read excel sheet with many tab ?

[#1]

Hi All

I have a excel file as per enclosed. I like to ask in order for me to read the raw data into QV ,there must be best approach. hope some one can advise me.

Instead of i read individual Tab one by one which is very trouble some , it there a better approach on short cut ?

Paul

[#2]

Hi SALTO,

Absolutely,

Then Paul can use the concatenate statement to keep appending data from multiple tabs in the XL

Paul:

Please try and tell us if this worked for you..

Regards,

Rochelle

[#3]

Hi John

Mr Salto already give me example using loop approach , which is the most effective way i think so.

Paul

[#4]

Hi John

Sure i will try it , and i believe it will work fine. but i need to make the all sheet header same for all old data.

Paul

[CELAMBARASAN]

You can use something like this

List the sheets in one new sheet

Sheets://Load the Sheet names from the "Sheets" Sheet

LOAD SheetName

FROM

temp1.xlsx

(ooxml, embedded labels, table is Sheets);

LET vNoofSheets = NoOfRows('Sheets') - 1;

LET vSheetName = Peek('SheetName', 0, 'Sheets');

TableName:

LOAD *,

    '$(vSheetName)' AS SheetName

FROM

temp1.xlsx

(ooxml, embedded labels, table is $(vSheetName));

For i=1 to $(vNoofSheets)

LET vSheetName = Peek('SheetName', $(i), 'Sheets');

Concatenate(TableName)

LOAD *,

    '$(vSheetName)' AS SheetName

FROM

temp1.xlsx

(ooxml, embedded labels, table is $(vSheetName));

NEXT

Hope the attachment helps

[#6]

Hi Sir

I try the loop script as mention on forum , I get below error :-

Field not found - <Date>

AllData:

LOAD

Date

FROM

[quotation list  2013.xlsx]

(ooxml, embedded labels, table is 10)

Enclsoed my QV doc.

Right now no error , because i comment the script on TAB=LOOP , if you un-comment it will see error. ( now my script read raw data with loop )

Paul

[salto]

Hello,

So sorry!

There was en error in the script I sent: instead of SheetNo it should say SheetName (in the loop):

For i = 0 to NoOfRows('InTable') - 1

    LET vSheet    =  peek('SheetName', $(i), 'InTable');

AllData:

LOAD *,

FROM ExcelSheetName.xlsx (biff, embedded labels, table is $(vSheet));

Regards.

[#8]

Hi Cel

you are good. because your script very short. what i need to do is make my header all same. all data will come in.

Paul

[#9]

Hi Salto

i still cannot figure out where go wrong , can you attach the file for me to check thru ?

[#10]

Ho

Hi Sir

i use my actual raw data ( because the sample raw data have few row of data only) , i change the file name = temp2 ( so that your temp1 still there )

then i use your qvw file = excel , i change to excel1 ( so that your excel.qvw still there )

I also notice you add new sheet at temp1 , i also add new sheet at temp2 , which have all the month name inside

then i go to excel2.qvw to change the loop script look for temp1 change to temp2

why i get below error.

Field not found - <SheetName>

Sheets:

LOAD SheetName

FROM

temp2.xlsx

(ooxml, embedded labels, table is Sheets)

Enclosed 2 file from you working , and 2 file from me not working. may be i misss out some thing.

[#11]

Hi Cel

I found the mistake , Tab name instead of sheets , i enter as sheet.

Now working fine.

Thank