6 Replies Latest reply: May 10, 2011 1:35 PM by 860836 RSS

    Export query to csv file

    860836
      I am almost new to Oracle, been a decade since I last worked on it. I have query over multiple tables. I need to run the query daily and get a csv file out of it. How sure how to do it. Can anyone please help? In the csv file I need no headers etc. just comma separated rows.

      I found this link which is very helpful, however, no matter what I try, the output file shows the query too. How do I make sure the output file has just the data and not the query?

      http://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus

      Edited by: 857833 on May 10, 2011 9:00 AM
        • 1. Re: Export query to csv file
          clcarter
          For sqlplus set echo off turns off the statement echo, but only when the sql is run from a file (@<file> or start <file>).

          For SQLs at the SQL> prompt, you'll still get the statement in the spool file :(
          • 2. Re: Export query to csv file
            860836
            So you are saying if I run the query at the sql prompt as from file, it might work? I will try that.

            However, I doubt what you are saying is correct, some one somewhere would've mentioned it? But I have no knowledge on this. Just a guess.

            I appreciate your response. This thing is driving me nuts. It is so simple and we can generate text files left and right all day long in mssql server. Too bad I couldn't find a comparatively simple solution in Oracle. :(
            • 3. Re: Export query to csv file
              clcarter
              In the 10g sqlplus user guide ... http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12040.htm#sthref2745
              • 4. Re: Export query to csv file
                860836
                This is getting very frustrating. Below is what I type in my sql plus window.
                set colsep ','
                set echo off
                set feedback off
                set linesize 1000
                set pagesize 0
                set sqlprompt ''
                set trimspool on
                set headsep off
                spool "FULLPATHTOOUTPUTFILE"
                @"FILEWITHQUERY.SQL"
                spool off


                This creates the output file, where first line is @"FILEWITHQUERY.SQL"
                then shows the result set rows, the last row in the file is "spool off"

                Can you please show me an example that works for you? I never imagined generating a text file from tables is this hard. :((
                • 5. Re: Export query to csv file
                  clcarter
                  Try putting the spool commands in the FILEWITHQUERY.SQL and if you add a `set termout off` you won't even see the results.
                  • 6. Re: Export query to csv file
                    860836
                    Thank you so much. That worked. I guess putting all the 'set's along with the query in one file sort of puts them all in one 'scope'.
                    It works perfectly now.