Important data before header

[#1]

Hello QV community - quick question:  I have a spreadsheet where the first row contains important data, the second row contains the headers, and then the data begins on row 3.  Here's an example:

Country:  USA

State^County^Sales

NY^Nassau^123456

NJ^Passaic^234567

OR^Multnomah^345678

I want to apply the country USA to every row.  Is there a way to do this in the load script?

Thank you

[Gysbert_Wassenaar · Accepted solution]

Temp:

First 1

Load subfield(@1,':' -1) as Country From MyExcelFile (ooxml, no labels, table is Sheet1);

LET vCountry = peek('Country');

Drop Table Temp;

Data:

LOAD

     '$(vCountr)' as Country,

     Subfield(@1, '^', 1) as State,

     Subfield(@1, '^', 2) as County,

     Subfield(@1, '^', 3) as Sales

FROM MyExcel File (ooxml, header is 3 lines, no labels, table is Sheet1);