Data separator by commas

[fabio182]

Hello experts, I have in the data load of a txt file, the separation of the data is by commas but there is a problem with a data column.  This column has commas in the data, which is why some data is not shown in its  respective column. Is it possible to control this problem?  Thank you very much for your comments.

RUC, NAME

2 20522471101,YOKOGAWA AMERICA DO SUL, LTDA. SUCURSAL

[Gysbert_Wassenaar · Accepted solution]

If your file has only two columns and the first column will never have a comma you can fix it with something like this:

LOAD
    SubField(@1, ',' 1) as RUC
    Mid(@1, Index(@1, ',')+1) as NAME
FROM
    MyTextFile.txt
    (txt, utf8, no labels, delimiter is ',', msq)
    ;

In general if the text with the comma isn't surrounded with quotes in the text file then Qlikview/QlikSense can't distinguish between comma's that should be used as field separators and comma's that should be part of the text. In that case you should go back to the system that generated the text file and make sure that when the text file is generated the text is quoted or that a different field separator is used. Otherwise you'll have to go through the text file manually and fix the problematic records yourself.

If you have to do it manually you could check which records are problematic. You could find out which records have a problem by adding two columns, one for the record number and one error_check column that will only have a value if the record has a problem because there are extra commas leading to a string value ending up in the error_check column.

LOAD
    RecNo() as Record,
    RUC,
    NAME,
    Error_Check
FROM
    MyTextFile.txt
    (txt, utf8, no labels, delimiter is ',', msq)
     ;

You can then select all values in the Error_Check field to see which records have a problem

[fabio182]

Excelent explanation my friend !!! You are genius