1 Reply Latest reply: Dec 28, 2017 10:36 AM by Petter Skjolden RSS

    CSV with commas in the last column

    Ido Perelshtein

      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.

        • Re: CSV with commas in the last column
          Petter Skjolden

          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;