Originally posted by: SystemAdmin

I would like to create a CSV file from data contained in a DB2 table. The description field on the table may contain commas or quotes, see below.

Steel 3/4" pipe
Brass, copper, steel and plastic pipe

I need to remove both the commas and quotes from the field as they mess up the Import process that the CSV file is used for. I used the SUBSTITUTE function to remove the commas.

SUBSTITUTE(Descrip,","," ")

That worked great. However, I have been unsuccessful in finding a method that will remove the quotes.

Any suggestions/ideas would be greatly appreciated.

Thanks in advance.

Ann

(Using Data Stage TX V 7.5.1 on Windows 2000 server.)
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

Try this:

code:1:8b44a89343=SUBSTITUTE(FieldName,"""","")[/code:1:8b44a89343]

or:

code:1:8b44a89343=SUBSTITUTE(FieldName,SYMBOL(34),"")[/code:1:8b44a89343]
John
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Might want to substitute "in." or "in" so you don't wind up with a huge diameter pipe.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Not sure if I follow how the quotes are getting there in the first place. I just dealt with " and other stuff dealing with flat files. To remove quotes from around all fileds you can go to the Input tab then the Format tab on you output file ... if you see Quote = double under the Field defaults then delete it. You can also set your deleimeter to whatever. One think=g I ran into was Format specs under the covers... you can double click on your column numbers in you Columns tab to see if there are any properties set there. Those properties override the file properties in the Input/Format tab. That cause me lots of trouble becuae I did not now you could set more properties for each cloumn and that they override the other settings.
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

I've come across the same problem quite often when building csv files. You can specify double quotes in the substitute function by using the HEXTEXTTOSTREAM function and using the value 22 (Value for double quotes):
Substitute(Field,HEXTEXTTOSTREAM("22")," ")
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Thanks John and Mike...
Your suggestions worked great.
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange