Text enclosure delimiter in CSV data

Six Stars

Text enclosure delimiter in CSV data

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?


Accepted Solutions
Thirteen Stars

Re: Text enclosure delimiter in CSV data

@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.

Manohar B

All Replies
Thirteen Stars

Re: Text enclosure delimiter in CSV data

@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.

Manohar B
Six Stars

Re: Text enclosure delimiter in CSV data

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