DevSupport
asked on
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_repor
spool '&spoolname'
prompt SERVERNAME,RETIREDDATE,APP
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.
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_repor
spool '&spoolname'
prompt SERVERNAME,RETIREDDATE,APP
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you read the documentation on the function? The first line in the documentation answers that question.
ASKER
Yeah, sorry but I tried this and it did not remove the newline which was inbetween the value in the cell.
select 'datacollection_full_repor
I will be working on putting the replace function at the source (where data is populated into the table) instead of read from table.
select 'datacollection_full_repor
I will be working on putting the replace function at the source (where data is populated into the table) instead of read from table.
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('datacolle
But, I don't think that the carriage return and/or line feed is showing up in the name of the spool file.
select replace(replace('datacolle
But, I don't think that the carriage return and/or line feed is showing up in the name of the spool file.
ASKER
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
from APEX.DATACOLLECTION; ?
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
from APEX.DATACOLLECTION; ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This worked for me which is same as your query but swapped the chr10 and chr13
Thank You so much Johnsone!
Thank You so much Johnsone!
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.
ASKER
I will test this and let you know.
Thank You!