Text enclosure delimiter in CSV data

[Anonymous]

I got a CSV file with mocked data. The delimiter is a comma and can not be changed. Some cell values have a comma inside the string as well. But those values are enclosed with double quotes. When I create a metadata from the CSV file inside Talend Open Studio and then use that metadata on a tFileInputDelimited, it reads the specific rows wrong and shifts the columns.

Here's a data example. See the third line whereas the account_name is quoted and contains a comma.

account_id,account_name,last_name,first_name,street,city,postal_code,country,email,card_number,card_type
0010F36071,DuBuque-Bernier,Iorns,Tessy,61503 Blaine Lane,Zhouzhuang,,CN,tiorns2@altervista.org,6333800116320639,switch
0010S53587,"Goodwin, Ernser and Sauer",McLinden,Kriste,2 Aberg Circle,Heishan,,CN,kmclinden3@boston.com,6761017442798625,maestro

What can I do?

[manodwhb · Accepted solution]

@Nagilo,since if you get data in Text enclosure with "",you can handle simple using by enabling CSV option in basic setting of tFileInputDelimited. if you do not get data with out text enclosure,then you data will see wrong data with comma delimited file,which has , as part of data.

[Anonymous]

Yeah, that worked. Thank you.

I used the field for the text enclosure the wrong way. Furthermore the preview when creating metadata from CSV file is not correct even if you set the text enclosure value correctly. But using it with tFileDelimited worked fine.

Thanks

Matthias

[Anonymous]

I tried this method, but my csv is still not delimiting correctly. See screenshot below. What am I missing?

[manodwhb]

@yichuansancun,your source data having issue,if you open your csv in notepad++ or some editor you will understand the issue. in this case you can request for correct format of source data or with same file you can remove in tjavarow or tmap using row1.col.romevAll("\\''","")

[Anonymous]

I opened the file in text editor and didnt see any error. See screenshot below. What do you mean by removing "\\" as i do not see that in the file:

  

And when I delimited this in Excel and other ETL tools, it worked just fine, somehow tFileInputDelimited in Talend is not able to recognize the double quotation.

[Anonymous]

What CSV options did you use?
tFileDelimitedImput isn't processing file with inconsitent format using and not using " enclousures. It don't identify well that data contains , inside fields, same as delimiter used.