DSXchange

Hi,
i want to replace single " (double) quote character with "" two double quotes in between field value.
for eg:-
1) field1 = "WEST WOODS" FIRE"
it should be replaced as "WEST WOODS"" FIRE"

2) field2 = "SCOT 1" J S FIST" KNOCK"
it should be replaced as "SCOT 1"" J S FIST"" KNOCK"

file is in csv format.

Can anyone help me out

Pongal,

The quotes inside strings read from CSV files can be changed from 1 to 2 via a EREPLACE('"','""',In.MyColumn1) derivation; the outer quotes in a csv file string are part of the file's definition and metadata, so you can only modify those in the sequential file stage definition.

But first you've got to read it into the job and those invalid inner quotes will make that... difficult.

Yes, so the next question's answer is:

If you read in the whole record as one string and parse it using a search pattern for "," to get valid string quotes plus the first character of the line and the last for the opening and closing ones. Any other quotes you can replace using the aforementioned method.

-Arnd.

Arnd

Ereplace alone doesn't work for the above examples.
i just want to add another double quote if any field value contains one double quote and i don't want to replace anything.
if i replace anything i may lose the exact data value.
i think you are understanding my point.

how do we do pattern matching for the above examples?
and another thing i need to take care some field values
have two double quotes(""), so i don't need to replace that value.
For eg: field3 = *"", then i should not replace here.

Can you explain me elaborately.

Pongal,

In my previous post I said you will need to do several things. I will go through and elaborate how I would do this.

1. Declare the input sequential file as having no delimiters and read in one column, calling it Big.

2. Convert the first " and the last " and all occurences of "," (the double quotes are part of what needs to be replaced) to single quotes.

3. This string has now replaced all of your text-file double quote delimiters with single quotes.

4. All double quotes left in this string are now part of the strings and not part of the sequential file CSV type string quotes.

5. You now want to replace and single double-quotes with another but leave any double double-quotes unchanged. You need to write some BASIC code to do this. I might try EREPLACE('"""','""',EREPLACE('"','""',In.Big)) to do this.

6. Write back to a non-quoted sequential file and then read from it using single-quotes as the quote character.

Hi,
I think you might want to set your character string to 000 (3 zeros) then you'll actually read the entire column including all " both around and inside your column.
After you get it you can manipulate the string to your liking.
you could per say trim leading & trailing " then double the remaining " and put that in Dqoute().

IHTH,