This discussion is archived
6 Replies Latest reply: Nov 20, 2012 8:03 PM by WhiteHat RSS

Spooling data in CSV Format

973608 Newbie
Currently Being Moderated
how can i make CSV format file through SQL.
  • 1. Re: Spooling data in CSV Format
    6363 Guru
    Currently Being Moderated
    select value1 || ',' || value2 from your_table;
    Or in sqlplus
    set colsep ','
    And use sqlplus spool command to create the output file

    http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#i2698758

    http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve043.htm#i2683777
  • 2. Re: Spooling data in CSV Format
    Solomon Yakobson Guru
    Currently Being Moderated
    That would work if columns do not have commas and double quotes. In general, you would use something like:
    '"' || replace(col1,'"','""',) || '","' || replace(col2,'"','""',) ... || '","' || replace(coln,'"','""',) || '"'
    {code}
    
    Also, we'd need to set proper linesize and set trimspool on.
    
    SY.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 3. Re: Spooling data in CSV Format
    vansul Pro
    Currently Being Moderated
    set lines 1000
    set pages 50000
    set head off
    spool d:\kul\myfile.csv

    select '"'|| col1|| '","'|| col2 ...... coln ||'""
    from <your table>
    where conditions. etc.
    /
    spool off

    now your file d:]\kul\myfile.csv is in the desired format.
  • 4. Re: Spooling data in CSV Format
    vansul Pro
    Currently Being Moderated
    You should replace the existing " with "" before you run the delimited query.
  • 5. Re: Spooling data in CSV Format
    rp0428 Guru
    Currently Being Moderated
    >
    how can i make CSV format file through SQL.
    >
    See this AskTom for a way to do it using pl/sql.
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1992907061984
  • 6. Re: Spooling data in CSV Format
    WhiteHat Expert
    Currently Being Moderated
    just thought I'd pop into this thread and mention that SQL developer has a few hints that may be useful if you're just after a quick ad-hoc solution:


    SELECT /*csv*/ * FROM scott.emp;
    SELECT /*xml*/ * FROM scott.emp;
    SELECT /*html*/ * FROM scott.emp;
    SELECT /*delimited*/ * FROM scott.emp;
    SELECT /*insert*/ * FROM scott.emp;
    SELECT /*loader*/ * FROM scott.emp;
    SELECT /*fixed*/ * FROM scott.emp;
    select /*text*/ * from SCOTT.EMP;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points