CSV with commas in the last column

[Anonymous]

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.

[petter]

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;