topic Re: read data using tFileInutDelimited with comma separated when there is comma and quetes in quoted string in Talend Studio

read data using tFileInutDelimited with comma separated when there is comma and quetes in quoted string

RanjithMeda — Fri, 15 Nov 2024 23:24:10 GMT

Hello All,

I am trying to read a csv file with comma separator where all the strings are in double quotes. So I selected CSV Options and entered """ in both escape characters section and Text Enclosure section and field separator as ,.

Now, I have few records where there is comma and " in one string and

"Apple" , "USA " , "0000012345" , "ACTV" , "Apple "LAC" Company HOME, *" , "A0BCDEF" , "34567890" , "0" , "0"

I am having issues with records like this where

"Apple "LAC" Company HOME, *" has

and

Because there is a double quote within the text, data after comma is being moved into next column though I have mentioned

text enclosure as "".

Please help me in reading such data using tfileInput

Re: read data using tFileInutDelimited with comma separated when there is comma and quetes in quoted string

Anonymous — Mon, 20 Dec 2021 13:04:42 GMT

I'm afraid It's impossible to read these data, there should exist escape character before comma included in the string, eg:

"Apple \"LAC\" Company HOME\, *","A0BCDEF"

Regards

Shong

Re: read data using tFileInutDelimited with comma separated when there is comma and quetes in quoted string

RanjithMeda — Thu, 23 Dec 2021 14:53:36 GMT

Thank you Shong!

Re: read data using tFileInutDelimited with comma separated when there is comma and quetes in quoted string

gjeremy1617088143 — Thu, 23 Dec 2021 15:14:24 GMT

Hi, maybe you could think of read the file as raw file apply this regex to replace " wich are not at the begin or end of a field : (?<!(,|^))"(?!(,|$))