Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi John
Mr Salto already give me example using loop approach , which is the most effective way i think so.
Paul
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
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
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
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.
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
Hi Salto
i still cannot figure out where go wrong , can you attach the file for me to check thru ?
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.
Hi Cel
I found the mistake , Tab name instead of sheets , i enter as sheet.
Now working fine.
Thank