oracle spooling query into csv eliminating new line character

Hi Experts,

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;
spool '&spoolname'
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.
DevSupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
I would look at the REPLACE function documented here.  To be safe, I would use it to remove carriage return and line feed characters.  Something like:

REPLACE(REPLACE(col_name,chr(10)),chr(13))
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DevSupportAuthor Commented:
Does this take care of CR and LF inbetween values in a cell? Like this "some\ntext" within a cell?

I will test this and let you know.

Thank You!
0
johnsoneSenior Oracle DBACommented:
Did you read the documentation on the function?  The first line in the documentation answers that question.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DevSupportAuthor Commented:
Yeah, sorry but I tried this and it did not remove the newline which was inbetween the value in the cell.

select 'datacollection_full_report_'||to_char(sysdate, 'yymmdd')||'_'||to_char(sysdate, 'hh24_mi_ss')||'.csv' replace(replace(spoolname,CHAR(13),' '),CHAR(10),' ') from dual;

I will be working on putting the replace function at the source (where data is populated into the table) instead of read from table.
0
johnsoneSenior Oracle DBACommented:
That statement is generating the name of the file.  That has nothing to do with the data.  If there is a carriage return in there, then you need to do this:

select replace(replace('datacollection_full_report_'||to_char(sysdate, 'yymmdd')||'_'||to_char(sysdate, 'hh24_mi_ss')||'.csv',CHAR(13)),CHAR(10)) from dual;

But, I don't think that the carriage return and/or line feed is showing up in the name of the spool file.
1
DevSupportAuthor Commented:
I am so sorry, I was mistaken,

This is the query which is getting the values.

select  SERVERNAME || ',' || RETIREDDATE || ',' || APPLICATION_DESCRIPTION || ',' || APP_LEVEL || ',' || PRPC_VERSION || ',' || TEST_GROUP || ',' ||  APP_ENGINE || ',' || APP_JAVA_VERSION || ',' || APP_OWNER || ',' || APP_PRIMARY_URL || ',' || APP_SECURE_URL || ',' || APP_F5_URL || ',' || APP_TOMCATMANAGER || ',' || FUNCTION || ',' || DBSERVER || ',' || DBNAME || ',' || DBVERSION || ',' || OS || ',' || CREATE_DATE || ',' || LOCATION || ',' || LICENSE || ',' || CPU || ',' || RAM || ',' || STORAGE || ',' ||  JVM_INITIAL || ',' || JVM_MAX || ',' || SSL_CERT_EXP_DATE || ',' || URL_OBFUSCATION || ',' || IP_ADDRESS || ',' || SMA_LOCKDOWN
from APEX.DATACOLLECTION;


If I want to use replace in all coulmns here, would it be like this?

select  replace(replace(SERVERNAME ||,CHAR(13)),CHAR(10)) ',' replace(replace(|| RETIREDDATE ||,CHAR(13)),CHAR(10)) and so on...
from APEX.DATACOLLECTION; ?
0
johnsoneSenior Oracle DBACommented:
You are concatenating all the columns into one.  So, why do the replace on each column.  Do it on the whole thing:
SELECT Replace(Replace(servername 
                       || ',' 
                       || retireddate 
                       || ',' 
                       || application_description 
                       || ',' 
                       || app_level 
                       || ',' 
                       || prpc_version 
                       || ',' 
                       || test_group 
                       || ',' 
                       || app_engine 
                       || ',' 
                       || app_java_version 
                       || ',' 
                       || app_owner 
                       || ',' 
                       || app_primary_url 
                       || ',' 
                       || app_secure_url 
                       || ',' 
                       || app_f5_url 
                       || ',' 
                       || app_tomcatmanager 
                       || ',' 
                       || FUNCTION 
                       || ',' 
                       || dbserver 
                       || ',' 
                       || dbname 
                       || ',' 
                       || dbversion 
                       || ',' 
                       || os 
                       || ',' 
                       || create_date 
                       || ',' 
                       || location 
                       || ',' 
                       || license 
                       || ',' 
                       || cpu 
                       || ',' 
                       || ram 
                       || ',' 
                       || STORAGE 
                       || ',' 
                       || jvm_initial 
                       || ',' 
                       || jvm_max 
                       || ',' 
                       || ssl_cert_exp_date 
                       || ',' 
                       || url_obfuscation 
                       || ',' 
                       || ip_address 
                       || ',' 
                       || sma_lockdown, Chr(13)), Chr(10)) 
FROM   apex.datacollection; 

Open in new window

1
DevSupportAuthor Commented:
This worked for me which is same as your query but swapped the chr10 and chr13

Thank You so much Johnsone!
0
johnsoneSenior Oracle DBACommented:
The order of the chr(10) and chr(13) should not matter.  REPLACE can only handle one thing at a time, so you need two.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.