Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

iperelshtein
New Contributor

CSV with commas in the last column

I've got a CSV file that has several types of inputs in the last column that includes commas, field name 'event info'

Is it possible to tell Qlik to ignore commas in the last field?

See attach file as example.

thanks.

Tags (1)
1 Reply
MVP
MVP

Re: CSV with commas in the last column

You can't modify a normal LOAD from a regular CSV-file to ignore commas in the last field.

However you can get it done by doing some parsing yourself with a bit more load scripting:

RAW_TEXT:

LOAD

    "@1:n" AS TxtLine

FROM [lib://Downloads/Events.csv]

(fix, codepage is 28591, no labels);

Labels:

FIRST 1 LOAD TxtLine AS Labels RESIDENT RAW_TEXT;

nLabels = SubStringCount(Peek('Labels'),',');

s = 'DATA: NOCONCATENATE LOAD' & Chr(10) & '   ';

FOR l=1 TO nLabels

  s = s & '  SubField(TxtLine,' & Chr(39) & ',' & Chr(39) & ',' & l & ') AS [' & Trim(SubField( Peek('Labels') , ',' , l )) & ']' & Chr(10);

  IF l < nLabels THEN

    s = s & '  ,';

  ELSE

    s = s & '  ,  Mid(TxtLine, Index(TxtLine,' & Chr(39) & ',' & Chr(39) & ',' & (nLabels) & ')+1 ) AS [' & Trim(SubField( Peek('Labels') , ',' , l+1 )) & ']';

  ;

  END IF

NEXT

s = s & Chr(10) & 'RESIDENT RAW_TEXT' & Chr(10) & 'WHERE RecNo()>1;' ;


TRACE $(s);

$(s);

DROP TABLE RAW_TEXT;