views:

47

answers:

1

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
+1  A: 

Typically, if a CSV file has commas or carriage-returns in the data then the field is double-quoted to identify that the field spans multiple lines.

1,ABC,1
2,DEF,2
3,"G
H
I",3
4,JKL,4

I'm not sure about the other characters, but this is the way I've seen this dealt with.

Adam Hawkes