Solved

oracle spooling query into csv eliminating new line character

Posted on 2017-03-30
9
54 Views
Last Modified: 2017-03-31
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.
0
Comment
Question by:DevSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 42072261
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
 

Author Comment

by:DevSupport
ID: 42073293
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
 
LVL 35

Expert Comment

by:johnsone
ID: 42073354
Did you read the documentation on the function?  The first line in the documentation answers that question.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DevSupport
ID: 42073688
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
 
LVL 35

Expert Comment

by:johnsone
ID: 42073706
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
 

Author Comment

by:DevSupport
ID: 42073791
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
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 500 total points
ID: 42073803
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
 

Author Comment

by:DevSupport
ID: 42073997
This worked for me which is same as your query but swapped the chr10 and chr13

Thank You so much Johnsone!
0
 
LVL 35

Expert Comment

by:johnsone
ID: 42074014
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ORA-02288: invalid OPEN mode 2 130
oracle forms question 22 61
Need help on decision table structure 7 58
Read CLOB data from Oracle using JAVA 3 43
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question