3

I am not a professional, sorry. But perhaps you can help me anyway ...

I have a table with 27 columns and within the rows data with linebreaks. When I try to export selected rows with sql-plus spool, I have several problems. I tried the advices given here: How to make sqlplus output appear in one line?, but they don't help me. I'll explain below.

First the configuration of the SQL plus query:

set term off    
set echo off    
set underline off    
set colsep ';'    
set linesize 32767    
set pages 10000    
set trimspool on    
set trimout on    
set feedback off    
set heading on    
set newpage 0    
set headsep off    
;set wrap off    
set termout off    
set long 20000    

spool D:\tmp_Datenaustausch\Export-141204.csv    

SELECT MEDIENNR, ISBN, ISBN2, ISBNSUCH, KATKARTE1, BUCHKARTE1, SIGNATUR1, KATKARTE2,
   BUCHKARTE2, SIGNATUR2, KATKARTE3, BUCHKARTE3, SIGNATUR3, MAB2, FEHLERCODE, FARBE, 
   ANZSYKKARTEN, KKSTATUS, BKSTATUS, MABSTATUS, SESTATUS, FARBSTATUS, ASSTATUS, 
   REZENSION, REZENSENT, STICHWORTE, UPDDATE FROM Bekartungsdaten WHERE upddate like 
   TO_DATE('2014-12-01', 'yyyy-mm-dd');    

spool off;    
exit;    
/    

My problems:

  1. Headings are not in one line, "set wrap off" does not help, because it truncates data.
  2. Some fields contain text with linebreaks, sql+ puts the lines of these fields in seperate fields in the csv. I need them in one field with linebreaks like stored in the database.
  3. How do I get quotationmarks at the beginning and end of each field/cell in the csv?

I would be very glad, if some could help me!

  • if you have line-breaks, how do you expect that to be in one line without replacing those characters? – Raj Dec 11 '14 at 15:21
  • I'm afraid sqlplus is not the right tool for CVS export. It's mighty tool, but lacks formating options. In case when column field contain quotes(for examples) it might be PITA to get correct CSV file. – ibre5041 Jan 10 '15 at 8:47
2

1) If you want the headings in one line, you have to use a UNION with a select from dual. Example:

select '"'||'col1'||'","'||'"col2"'||'","'||'"col3"'
  from dual
union
select '"'||col1||'","'||col2||'","'||col3||'"'
  from table;

2) This one is tricky. It helps to know what ascii character your line breaks actually are. Depending on how they were written, generally (but not always) they will be chr(10) or a combination of chr(10)chr(13). You can use the REPLACE function in your query to replace those with '', which should get rid of them.

3) If you want quotes, see my example #1 above.

HTH.

0

If you want to have headline of the record you better try this:

set linesize 220;
set pagesize 0;
SET NEWPAGE 0;
SET SPACE 0;
SET ECHO OFF;
SET FEEDBACK OFF;
SET VERIFY OFF;
SET HEADING OFF;
SET MARKUP HTML OFF SPOOL OFF;

spool D:\tmp_Datenaustausch\Export-141204.csv   

select 'header1'         ||','||
       'header2'         ||','||
       'header3'                  
result from dual
union all
select a    ||','||
       b    ||','||
       c      
from tableName
where coditional ;
spool off;
exit;

the result will be:

header1  | header2  | header3|
-------------------------------
a        | b        | c

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.