jeudi 4 décembre 2014

How to format sql-plus-spool-file to *.csv with all columns in one line and row-content with linebreaks as one field?


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-querry:



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!


Best regards Christoph Holzapfel





Aucun commentaire:

Enregistrer un commentaire