Hi all,
I'm trying to read a csv file and convert it to an output file to be read by another program. The problem I'm having is with embedded single quotes in the input file.
For example, if I have in the input csv file:
123,Michael's Place,123 Main St.
I want the output to look like:
'123' 'Michael''s Place' '123 Main St.'
It's easy enough to surround the strings with the single quotes, but how can I replace one single quote with two single quotes?
Convert function won't work because it's a one to one replacement for each character in the list and new.list.
Are there any other tricks or functions I could use?
Replacing single quote with two single quotes in Transformer
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 57
- Joined: Tue Jun 30, 2009 9:38 am
Is this a server or a parallel job and, if it is a parallel job, can it be made into a server job or use a BASIC transform stage - in which case the function would work for you. If the number of single quotes is limited to one or two, then you can use a PX transform stage with stage variables and utilize subsequent INDEX(YourString,"'",1) to find the first occurrence then modify the string with a NewString = OldString[1,SingleQuotePos]:"'":OldString[SingleQuotePos+1,9999]
Code: Select all
EREPLACE(YourString,"'","''")
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: