topic Re: Text enclosure delimiter in CSV data in Talend Data Catalog

Text enclosure delimiter in CSV data

Anonymous — Thu, 19 Jul 2018 16:51:39 GMT

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?

Re: Text enclosure delimiter in CSV data

manodwhb — Fri, 20 Jul 2018 05:35:42 GMT

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

Re: Text enclosure delimiter in CSV data

Anonymous — Fri, 20 Jul 2018 08:17:40 GMT

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

Re: Text enclosure delimiter in CSV data

Anonymous — Sun, 30 Dec 2018 22:25:32 GMT

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

Re: Text enclosure delimiter in CSV data

manodwhb — Mon, 31 Dec 2018 04:19:12 GMT

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

Re: Text enclosure delimiter in CSV data

Anonymous — Mon, 31 Dec 2018 18:57:42 GMT

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.

Re: Text enclosure delimiter in CSV data

Anonymous — Thu, 04 Apr 2019 07:33:32 GMT

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.