I am attaching a sample file for your reference.
In the attached file I have three tables with table header (Transaction 1, Transaction 2, Transaction3...) and always I want to load data from transaction 2. How will I achieve this.
Format.xlsx 12.1 K
Thanks for sharing above line, but it doesn't work for me. In my case I have a file ,say Transaction 02-02-2016.xlsx. This files contain data divided in table format. Sample excel files has been attached above. I want to load data from single table "Transaction 2" and leave other tables untouched.
Add this to script. Gave me what I think you looking for but as Jonathan pointed out, will need more detail if it does not as this can get a little complicated to dynamically account for
LOAD *, RowNo() as PQRRows
(ooxml, no labels, table is Sheet1);
LOAD PQRRows as PQRStartEnd
WHERE A = 'PQR' or A = 'MNO';
LET zRowStart = Peek('PQRStartEnd',0)+2;
LET zRowEnd = Peek('PQRStartEnd',1);
A as No,
B as Name,
C as Gender,
D as Address,
E as Phone
WHERE PQRRows >= $(zRowStart) and PQRRows < $(zRowEnd) and (not isnull(A) or A='');
DROP TABLES PQRRowsTABLE, PQRStartEnd;
Try something like below,
Temp: LOAD RecNo() as DataStart From Format.xlsx (ooxml, No Labels, Table is Sheet1) WHERE A = 'Transaction2'; LET vHeaderSize = Peek('DataStart'); DROP TABLE Temp; Data: LOAD [Job ID] ,[Job Name] ,[Job Start Date] ,[Job End Date] ,[job status] From Format.xlsx (ooxml, embedded Labels, Header is $(vHeaderSize) Lines, Table is Sheet1) Where Not IsNull([Job Name]) and [Job ID] <>'Job ID' ;
Sample QVW file attached against your file. Let me know
Test1.qvw 152.5 K
I misunderstood question. I thought the OP wants to load data from second transaction. I guess the below one should work in that case.
RecNo() as DataStart
(ooxml, No Labels, Table is Sheet1)
WHERE A = 'Transaction2';
LET vHeaderSize = Peek('DataStart');
DROP TABLE Temp;
LOAD [Job ID]
,[Job Start Date]
,[Job End Date]
(ooxml, embedded Labels, Header is $(vHeaderSize) Lines, Table is Sheet1)
Where RecNo()= Autonumber([Job Name]);
I was trying to simplify the script.
That's really clever with the where clause, I like that!
Without knowing how the data is populated I wonder what would be the best approach. From a simplicity perspective, your solution requires less lines of code to get the desired result. In my solution, more lines are required but the constraint is not on any dimensional value so you always guaranteed to get just transaction 2, unless transaction 3 name changes. Likewise with the RecNo() approach, if Job Name happens to be named the same for any transaction then incorrect records would get fetched. So the two solutions both have constraints, one at a 'table name' level and the other at the field level.