How to import a csv where one header is empty and preserve the * in load statement?

[#1]

Scenario;

In the load editor we want to use the * symbol instead of listing all header names individual to keep flexibel.

Type is .csv

The header names are located in row 1

Issue is that "cell" B1 has a empty header name.

How can I use the "embedded labels" names are in row 2 without loosing the * in the load statement?

code...

set Import_Location = 'random netwerk location';

load * inline [
FIELD
A
B
C

];

FOR Each i in FieldValueList('FIELD')
    Trace #### LOAD_DATA_IN_MEMORY ####;
 
      LOAD_DATA: 
        LOAD distinct
             Date(Date#(Textbetween(UPPER(Filename()),'$(i)'  & '.','.CSV'),'YYYYMMDD')) As Date,
             *
         FROM  [$(Import_Location)]$(i).*.csv
           (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
         where left([ID],10) <> 'Disclaimer' and [ID] <> 'Total';

.........

[petter]

This script should give you an idea on how to select column headers from any number of initial rows - in this case the first two rows:

CH:
FIRST 2 LOAD * FROM [your-spreadsheet.xlsx] (ooxml, no labels, table is Sheet1);
theCols = '';
cols = NoOfFields('CH');
FOR c = 1 TO cols
  row = If( IsNull( Peek(FieldName(c,'CH'),0,'CH') ) , 1 , 0 ); 
  new = Peek(FieldName(c,'CH'),row,'CH'); 
  theCols = theCols & '[' & FieldName(c,'CH') & '] AS [' & new & ']' & If( c < cols , ',');
NEXT
cols=;c=;row=;new=;
DROP TABLE CH;

DATA: 
LOAD 
  $(theCols) 
FROM 
  [your-spreadsheet.xlsx] (ooxml, no labels, table is Sheet1)
WHERE RecNo() > 2;
theCols=;

[marcus_sommer]

I think this won't be possible - either you could use the wildcard-logic as it is or not respectively you will need additional steps like loading the first two records and building a fieldlist or a mapping-table from these data per functions like fieldname(), peek() and if() + some string-functions within loops. Here a few examples to similar cases:

Re: how to delete specific column number from CSV loading?

Re: RENAME USING - with QUALIFY?

Re: How to modify the same values in more than hundred distinct fields?

- Marcus

[#4]

Ok Petter, with a bit of tweeking it worked. Kept about 70% of you`re code, tnx