CODEWITHSUNDEEP
csvtalend
StatguyUser
StatguyUser

Reputation: 2665

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

Upvotes: 0

Views: 1058

Answers (2)

Maira Bay
Maira Bay

Reputation: 300

As TRF mentioned, you can't have the delimiter as part of the non-delimiting text in your file.

My workaround for that would be the following:

1) Read the file with a tFileInputFullRow (https://help.talend.com/display/TalendComponentsReferenceGuide54EN/tFileInputFullRow)

2) Use a tReplace to replace the ; with some other character, say -, for the problem cells (in your case, replace "New York;USA" with "New York-USA". You can also use the regex option in the tReplace component to make it a generic rule.

3) Save that output into another file

4) Now read the new file using ; as the delimiter

References:

1) tReplace: https://help.talend.com/display/TalendOpenStudioComponentsReferenceGuide521EN/18.16+tReplace

2) Regex: https://docs.oracle.com/javase/tutorial/essential/regex/

Upvotes: 1

TRF
TRF

Reputation: 801

I think you cannot have the field separator included in the field content or you have to incluse these values between "". For example: blabla;"New York; USA";blabla
Other solution, change the field delimitor to a more specific (and unused) character.

I'm afraid there is no better solution.

Regards, TRF

Upvotes: 2

Related Questions