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

[RanjithMeda]

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

[Anonymous · Accepted solution]

Hi

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

[RanjithMeda]

Thank you Shong!

[gjeremy1617088143]

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 : (?<!(,|^))"(?!(,|$))