Six Stars

Read a list of CSV files in Talend with ; in field

I have a list of CSV files which i receive for ETL into database every month. Its in a folder. My data has ; in many columns as well. For example, in the location column values like New York; USA are present, which i want to appear in a single column instead of splitting into many columns. How do i specify delimiter then?

 

enter image description here

 

enter image description here

 

 Edit: My data is in a csv file and looking like below, it has 3 columns id, Introduction and Verical. I want to remove comma(,) appearing in the values of each column and then save results as a csv file with 3 distinct columns with their values.

id	Introduction	Vertical
Jinshan District, Shanghai, China	international saler at Shanghai Triowin Automation Machinery Co.,Ltd	Machinery, Transportation
Pudongxin District, Shanghai, China	Market Communication Specialist, Shanghai JiaoTong University - Kedge Business School	Education Management
Nanjing City, Jiangsu, China	System Tester at Jiangsu HopeRun Software Co., Ltd.	Computer Software

I want output in 3 separate columns without comma values like below.

 

 

id	Introduction	Vertical
Jinshan District Shanghai China	international saler at Shanghai Triowin Automation Machinery Co.Ltd	Machinery Transportation
Pudongxin District Shanghai China	Market Communication Specialist Shanghai JiaoTong University - Kedge Business School	Education Management
Nanjing City Jiangsu China	System Tester at Jiangsu HopeRun Software Co. Ltd.	Computer Software

 

  • Data Integration
1 ACCEPTED SOLUTION

Accepted Solutions
Six Stars

Re: Read a list of CSV files in Talend with ; in field

Ok, this is what worked for me. A simple thing, all i did was changed Encoding to UTF-8 in the Advanced settings tab in iFileInputDelimited component..

9 REPLIES
Community Manager

Re: Read a list of CSV files in Talend with ; in field

Hi
Can you give us an example of your data to explain your requirement? What are your expected result?

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Nine Stars TRF
Nine Stars

Re: Read a list of CSV files in Talend with ; in field

Values with ";" must be enclosed between " or change the field delimiter.
Otherwise regex may help if you have a pattern to follow.

TRF
Six Stars

Re: Read a list of CSV files in Talend with ; in field

My data is in a csv file and looking like below, it has 3 columns id, Introduction and Verical. I want to remove comma(,) appearing in the values of each column and then save results as a csv file with 3 distinct columns with their values.

id	Introduction	Vertical
Jinshan District, Shanghai, China	international saler at Shanghai Triowin Automation Machinery Co.,Ltd	Machinery, Transportation
Pudongxin District, Shanghai, China	Market Communication Specialist, Shanghai JiaoTong University - Kedge Business School	Education Management
Nanjing City, Jiangsu, China	System Tester at Jiangsu HopeRun Software Co., Ltd.	Computer Software

I want output in 3 separate columns without comma values like below.

 

id	Introduction	Vertical
Jinshan District Shanghai China	international saler at Shanghai Triowin Automation Machinery Co.Ltd	Machinery Transportation
Pudongxin District Shanghai China	Market Communication Specialist Shanghai JiaoTong University - Kedge Business School	Education Management
Nanjing City Jiangsu China	System Tester at Jiangsu HopeRun Software Co. Ltd.	Computer Software
Nine Stars

Re: Read a list of CSV files in Talend with ; in field

I believe this is the same issue as your other topic? Set your Field Separator to "," and your Text Enclosure to "\"" and you should be good.
Six Stars

Re: Read a list of CSV files in Talend with ; in field

Not working! Can you post a screenshot if in case it worked for you?

Nine Stars

Re: Read a list of CSV files in Talend with ; in field

Nine Stars TRF
Nine Stars

Re: Read a list of CSV files in Talend with ; in field

once you have " around fields, once you have not, it's difficult to give you the answer if the question change from a topic to another.

please, for people trying to help others, be constant and don't open multiple topics for the same question.


TRF
Five Stars

Re: Read a list of CSV files in Talend with ; in field

Six Stars

Re: Read a list of CSV files in Talend with ; in field

Ok, this is what worked for me. A simple thing, all i did was changed Encoding to UTF-8 in the Advanced settings tab in iFileInputDelimited component..