9 Replies Latest reply on Jul 22, 2011 6:54 AM by Marwim

    Saving sql query result as excel file

      I'm a newbie to database/Oracle.
      I need to output the query result into a Excel file. This need to be done through the script/query itself not with any tools.
      Eg. Select * from empoyees ---> into an excel file .
      How can this be achieved?
      Any help is appreciated.

        • 1. Re: Saving sql query result as excel file
          Using PL/SQL you have a few approaches.


          Would be one option to create a CSV file. If you need an Excel file, then you're going to have to look into something like SYLK
          • 2. Re: Saving sql query result as excel file
            As Tubby said above, a CSV file is one way (and likely the simplest way).

            Just in case, there is no "magic" that will take the output of:
            select * from <sometable>; 
            and turn it into anything that is readable in Excel. You will need to do a TINY amount of work, which is, you will have to name each column in your select and separate them with commas yourself. Something like the following:
            select column1, ',' column2, ',' column3 from whatever_table;
            if you execute a statement like the one above in SQL*Plus with the proper options (so you don't get column headings, separating lines and the like) and spool it into a file you will end up with something that Excel will be very happy with.

            Here is a complete example of what you have to do, study it, it is really easy to understand:
            --This script extracts data from the employee
            --table and writes it to a text file in
            --a comma-delimited format.
            --Set the linesize large enough to accommodate the longest possible line.
            SET LINESIZE 80
            --Turn off all page headings, column headings, etc.
            SET PAGESIZE 0
            --Turn off feedback
            SET FEEDBACK OFF
            --Eliminate trailing blanks at the end of a line.
            SET TRIMSPOOL ON
            SET TERMOUT OFF
            SPOOL current_employees.csv
            SELECT '"ID","Billing Rate","Hire Date","Name"'
            FROM dual;
            SELECT    TO_CHAR(employee_id) || ','
                   || TO_CHAR(employee_billing_rate) || ','
                   || TO_CHAR(employee_hire_date,'MM/DD/YYYY') || ','
                   || '"' || employee_name || '"' 
            FROM employee
            WHERE employee_termination_date IS NULL;
            SPOOL OFF
            Credit where it is due: the code above is from "Oracle SQL*Plus: The Definitive Guide by Johnathan Gennick"



            Edited by: 440bx - 11gR2 on Aug 1, 2010 7:27 PM - Added due Credit
            • 3. Re: Saving sql query result as excel file
              In addition to that, it's easier to use the COLSEP command than to manually append them in yourself :)
              TUBBY_TUBBZ?set colsep ,
              TUBBY_TUBBZ?select * from scott.emp;
                   EMPNO,ENAME     ,JOB      ,       MGR,HIREDATE            ,       SAL,      COMM,       DEPTNO
                    7369,SMITH     ,CLERK    ,      7902,17-DEC-1980 12 00:00,       800,          ,           20
                    7499,ALLEN     ,SALESMAN ,      7698,20-FEB-1981 12 00:00,      1600,       300,           30
                    7521,WARD      ,SALESMAN ,      7698,22-FEB-1981 12 00:00,      1250,       500,           30
                    7566,JONES     ,MANAGER  ,      7839,02-APR-1981 12 00:00,      2975,          ,           20
                    7654,MARTIN    ,SALESMAN ,      7698,28-SEP-1981 12 00:00,      1250,      1400,           30
                    7698,BLAKE     ,MANAGER  ,      7839,01-MAY-1981 12 00:00,      2850,          ,           30
                    7782,CLARK     ,MANAGER  ,      7839,09-JUN-1981 12 00:00,      2450,          ,           10
                    7788,SCOTT     ,ANALYST  ,      7566,19-APR-1987 12 00:00,      3000,          ,           20
                    7839,KING      ,PRESIDENT,       ,17-NOV-1981 12 00:00,      5000,          ,           10
                    7844,TURNER    ,SALESMAN ,      7698,08-SEP-1981 12 00:00,      1500,         0,           30
                    7876,ADAMS     ,CLERK    ,      7788,23-MAY-1987 12 00:00,      1100,          ,           20
                    7900,JAMES     ,CLERK    ,      7698,03-DEC-1981 12 00:00,       950,          ,           30
                    7902,FORD      ,ANALYST  ,      7566,03-DEC-1981 12 00:00,      3000,          ,           20
                    7934,MILLER    ,CLERK    ,      7782,23-JAN-1982 12 00:00,      1300,          ,           10
              14 rows selected.
              • 4. Re: Saving sql query result as excel file
                Nice "trick" !!

                I hadn't thought about that. Thank you, Tubby :)

                • 5. Re: Saving sql query result as excel file
                  Hello Harshini,

                  if you need a simple CSV you can use one of the solutions mentioned above.
                  If you need an "Excel" file that allows for formatting or multiple worksheets you can use one of the following:

                  https://xml-spreadsheet.samplecode.oracle.com/ (it accepts a query or a cursor as argument) or
                  Others can be found at Re: How to save a query result and export it to, say excell?

                  • 6. Re: Saving sql query result as excel file
                    Thanks Tubby and everyone, who took time to help me

                    Edited by: 785761 on Aug 1, 2010 11:19 PM
                    • 7. Re: Saving sql query result as excel file
                      Many times we work in environments where we are not the dba of the production databases and we cannot create packages or user defined types to do our job. for example, How can we create an xml (xls) file if only we have a SELECT ANY DICTIONARY privillege?. Of course, we can use many Oracle tools to do that, one of them is SQL Developer but if we need to execute many SQL Statements we have to export as XLS document each SQL output and this is very tiring. Becouse of that, I recomend the method used in this link:


                      Maybe is not the correct solution but it was very usefull for me.

                      Hope this help and not a spam. :P


                      Edited by: johnxjean on 06-Dec-2010 19:15
                      • 8. Re: Saving sql query result as excel file
                        dont worry so much... Use something like the below:

                        SPOOL C:/MY_TEST_FILE.xls;
                        SELECT * FROM EMP;
                        SPOOL OFF;

                        • 9. Re: Saving sql query result as excel file
                          Hello Avi,

                          welcome to the forum. Is there a special reason why you try to reanimate an old, already answered thread?