Problem With Comma Separated File
Moderators: chulett, rschirm, roy
Problem With Comma Separated File
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
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
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 107
- Joined: Wed Sep 29, 2004 10:15 am
-
- Charter Member
- Posts: 27
- Joined: Tue Nov 02, 2004 5:34 pm
Re: Problem With Comma Separated File
Use soem kind of Ascii Characheter instead of Comma as field separator
Thanks,
Ramesh
Thanks,
Ramesh
Thanks,
Ramesh
Ramesh
Re: Problem With Comma Separated File
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
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
If you have embedded field delimiters in your data, your only recourse is quoted fields. Set the quote character definition.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Folks, if the Sequential stage can't read the file correctly, then you can't do fixes like EREPLACE or CHANGE.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle