oracle spooling query into csv eliminating new line character
Posted on 2017-03-30
I am using spool to export a table into a csv file.
I am using the following to create the spool file:
set lines 10000
set trimspool on;
set feedback off;
set tab off;
set space 0;
set pages 0;
set term on;
/*set pagesize 10000;*/
/*set pagesize 5000;*/
col spoolname new_value spoolname
select 'datacollection_full_report_'||to_char(sysdate, 'yymmdd')||'_'||to_char(sysdate, 'hh24_mi_ss')||'.csv' spoolname from dual;
prompt SERVERNAME,RETIREDDATE,APPLICATION_DESCRIPTION,APP_LEVEL,PRPC_VERSION,TEST_GROUP,APP_ENGINE,APP_JAVA_VERSION,APP_OWNER,APP_PRIMARY_URL,APP_SECURE ..........from apex.table;
The table is auto-generated so some of cells have a newline character. In database table it looks ok but in the csv file it writes the new line and breaks the file formatting totally.
I would like to know if there is a way to spool file such that the newline character or space is ignored when csv file is generated.
From the script which generates the values its kind of difficult to eliminate , so I wish there is a way using spool to ignore that newlines.