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

 

Four Stars

Re: Text enclosure delimiter in CSV data

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

 

Item-0_and_Item-0_and_Item-0_and_Item-0_and_Item-0_and_Item-0_and_Item-0.png

Thirteen Stars

Re: Text enclosure delimiter in CSV data

@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("\\''","")

Manohar B
Highlighted
Four Stars

Re: Text enclosure delimiter in CSV data

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:

PMDOR_Trended_Worker_Nov2018_csv.png  

 

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.

 

Cloud Free Trial

Try Talend Cloud free for 30 days.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.