6 Replies Latest reply: Nov 20, 2012 10:03 PM by 523861 RSS

    Spooling data in CSV Format

    973608
      how can i make CSV format file through SQL.
        • 1. Re: Spooling data in CSV Format
          6363
          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
            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
              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
                You should replace the existing " with "" before you run the delimited query.
                • 5. Re: Spooling data in CSV Format
                  rp0428
                  >
                  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
                    523861
                    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;