Problem With Comma Separated File

[bapajju]

Hello,

I have a file that is Comme Separated file. But problem is that in value of the field itself I am getting "Comma" in between.

e.g.

"1965","LONDON","PETE, QUARTERS","TRACKSAVE","1234"

For the Third field how do i get rid of the "Comma" inside the string itself.

Thanks
Bapajju

[lclapp]

If the comma is always in the same field the then Ereplace function would be one way to make the comma a space. Also I try to never utilize a comma as the separator unless I have no choice. Using a | (pipe) and no "'s seems to keep DS Server happy.

[vmcburney]

Your file is perfectly valid as it uses string delimiters and comma seperators. If you setup your sequential file input stage to recognise " as delimiters then it will handle any commas inside the " ".

To remove the comma from your data try a server routine that receives the string as a job parameter and passes it back with the comma remove. The EReplace command can replace with an empty string but this may corrupt your data. Do you want PETE, QUARTERS to become PETE QUARTERS or do you want it to become QUARTERS PETE. Normally in a name field the comma indicates the surname was listed first followed by the first name.

Doing it in a routine gives you the chance to test your code using the Test... button and put a bunch of different names into the test fields.

[rkdatastage]

Ereplace function can replace the comma in the field , at the same time if u want to replace the comma with any other character like ('.' - dot) where u can preserve the meaning of the data. By using "" will have no impact of comma as the entire data in the field is treated as string.

[rkacham_DSX]

Use soem kind of Ascii Characheter instead of Comma as field separator

Thanks,
Ramesh

[NBALA]

Hi Bapajju or All,
How you solved this?

I have the same issue name field contains comma like "Commerce, Inc."
Using 7.5 server edition on windows. How to handle this while defining the file?.

Thanks in advance
-NB

[kcbland]

If you have embedded field delimiters in your data, your only recourse is quoted fields. Set the quote character definition.

[NBALA]

Thanks Ken,

In the meantime , I have requested the extract programmers to change the delimiter 'comma' to pipe "|". Works fine now.

-NB

[kcbland]

You stated you "have" a file, which to me meant you can't change it, so I answered how to live with it. If you can get pipes, great, just make sure there aren't pipes in the data. Otherise, use quotes.

[pkomalla]

Hi Bapajju,

Did you solve the problem or else try this

change(change(change(coulmn name,'","','|'),',',' '),'|','","')


I think this should work. It first converts "," to | and then changes the comma with in a string value to space and finally converts | back to ","

[kcbland]

Folks, if the Sequential stage can't read the file correctly, then you can't do fixes like EREPLACE or CHANGE.