Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

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

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?

Capture.PNG

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';

.........

3 Replies
MVP
MVP

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

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=;

MVP & Luminary
MVP & Luminary

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

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

Not applicable

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

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