Load multiple XLS and use certain fields as headlines for tables

[f1234567]

Hi QV-experts,

It seems I have troubles in solving following Task (in QV 11.20).

I have multiple invoices to load, which are xls-files, always from 2nd sheet (@2).

This would work fine, if I only had to load line 8-10 or 8-11 from sheet @2.

But: I need  certain fields as column headers, in order to know the customer Name, Contract ID and invoicing period for each xls-file loaded.

This Information is (always) stated in Column A line 1,3 and 5.

Hence, I would need to create an additional column (I) = ContractID (stated in A1), column (J) = Period (stated in A3), and column (K) = CustomerName (stated in A5)

Do you have an idea of how to proceed? Thanks in advance!

[prieper]

Suggest to load in two steps,

First to load the header-info, write them into variables

Second to load the invoice and populate add fields with the above variables

This embedded in an loop

(aircode)

FOR EACH sFile IN ('....')

Header:

LOAD * FROM $(sFile) [string for xls or xlsx - no header] WHERE RECNO() < 5;

LET vContract = SUBFIELD(PEEK('@A', 1, 'Header'), 'Contract ID ', 2);

LET vInvoicePeriod = ....;

Invoice:

LOAD

*,

'$(vContract)'     AS Contract,

'$(vInvoicePeriod)' AS InvoicePeriod,

...

FROM $(sFile) [string for xls or xlsx, embedded labels, header is 5 lines]

WHERE

ProductName <> 'Total';

NEXT sFile

HTH

Peter

[f1234567 · Accepted solution]

Thx Peter! Very helpful.

My collegue finalized the task. Here's the code:

FOR EACH sFile IN FileList('C:\Temp\*.xls')

Let sFile1 = '$(sFile)';


Header:
LOAD *
FROM $(sFile1) ( biff, no labels, Table is @2)
WHERE RECNO() < 7
;

Let vZeile1 = PEEK('@1', 0, 'Header');
Let vZeile2 = PEEK('@1', 1, 'Header');
Let vZeile3 = PEEK('@1', 2, 'Header');
Let vZeile4 = PEEK('@1', 3, 'Header');
Let vZeile5 = PEEK('@1', 4, 'Header');



Invoice:
LOAD
*,
'$(vZeile1)'  AS ContractID,
'$(vZeile3)'  AS InvoicePeriod,
'$(vZeile5)'  AS Customer

From $(sFile1) ( biff, no labels, Table is @2)
WHERE not WildMatch(@1, '*ProductName*') and Len(@2)>'2'
;


DROP Table Header;

NEXT sFile