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
Forteen 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
Don't forget to give kudos/accept the solution when a replay is helpful.

All Replies
Forteen 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
Don't forget to give kudos/accept the solution when a replay is helpful.
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

Forteen 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
Don't forget to give kudos/accept the solution when a replay is helpful.
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.

 

Four Stars

Re: Text enclosure delimiter in CSV data

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.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch