Skip to Main Content
  • Questions

  • formatting output from sql command generated output csv files

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Volkenand.

Asked: January 16, 2017 - 3:49 pm UTC

Last updated: January 18, 2017 - 3:03 pm UTC

Version: 4.0.01.14

Viewed 100+ times

You Asked



From sql-developer window:
how it is possible to get every column value from SQL select to csv file in a seperate column in CSV

currently the whole output is in column A for every data set, when I click in Excel on the line, I see it in the view (White space) in the upper part of the window.

my script

set COLSEP  | 
set verify off
set feedback off
set trimspool on
spool on

set term off
set feed off

--spool h:\Dokumente\SL2-DST-frei-1-9-gewerkschaft.xls
--spool W:\SG-323\TeamZentraleKatalogredaktion\+_vk\Sonderlocken-u-RU\Excel-csv-txt\dst_polizei201701.txt
spool W:\SG-323\TeamZentraleKatalogredaktion\+_vk\Sonderlocken-u-RU\Excel-csv-txt\SL2-DST-frei-1-9-gewerkschaft-q4.csv

select * from DST order by DSTSCHL;


spool off
--Einstellungen für spool zurücknehmen
set verify on
set feedback on

and Chris said...

You can read about formatting query output for CSV at:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9518534700346581975

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

columns with header for output

Volkenand Kerstin, January 17, 2017 - 2:03 pm UTC

the link below is useful. But in my SQL select I have it with different headers as below :

select DSTDATV AS "Gültigkeitsdatum -von", DSTDATB AS "Gültigkeitsdatum -bis"

Where do I insert the || '|' || ; after column Name or the Heading for csv document ?
Both I tried and it throws an SQL error:
Connor McDonald
January 18, 2017 - 12:36 am UTC

Sorry - I'm not sure what you mean

SQL> select dummy AS "Gültigkeitsdatum -von", dummy AS "Gültigkeitsdatum -bis"
  2  from dual;

Gültigkeitsdatum -von            Gültigkeitsdatum -bis
-------------------------------- --------------------------------
X                                X


You need to elaborate more and show us

spool output columns seperated

Volkenand Kerstin, January 18, 2017 - 8:05 am UTC

additional question: where do I add the || ';' ||, when I have defined additional Header names for the Output?
I have tried after the column Name and before the ;, but there are always SQL errors

select DSTDATV AS "Gültigkeitsdatum -von", DSTDATB AS "Gültigkeitsdatum -bis" ...further columns

Chris Saxon
January 18, 2017 - 3:03 pm UTC

See

http://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.