I'm having a problem that I can't figure out while converting a table field in an Oracle database to a CSV file. I have a column of varchar2(4000) where users enter their comments. Sometimes the users copy and paste text from other programs such as MS Word therefore, a lot of extra information is pasted into the field such as carriage returns and line feeds. There can also be large amount of text in this field. For some reason, even though I get rid of the carriage returns and line feeds, the CSV file still shows a CR and LF which causes the data to be separated into two rows when I open the CSV file in Excel. Below is my code for just this column. Can someone help me figure out why this is happening and if there is a way to fix the issue? Any help is much appreciated. Thanks.
SET RECSEP OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET TERMOUT OFF
SET TERM OFF
SET ECHO OFF
SET VERIFY OFF
SET HEAD OFF
SET PAGESIZE 0
SET LINESIZE 3000
SET COLSEP '|'
SET FEEDBACK OFF
SET WRAP ON
spool 'C:\Temp\data.csv'
SELECT REPLACE (REPLACE (TRIM(COMMENTS), chr(13),''), chr(10), '')
FROM T_COMMENT;
SPOOL OFF