9 Replies Latest reply: Jul 22, 2011 1:54 AM by Marwim RSS

    Saving sql query result as excel file

    788764
      Hi,
      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.

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

          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:68212348056

          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
          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:769425837805
          • 2. Re: Saving sql query result as excel file
            783956
            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"

            HTH,

            John.

            Edited by: 440bx - 11gR2 on Aug 1, 2010 7:27 PM - Added due Credit
            • 3. Re: Saving sql query result as excel file
              Tubby
              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.
              
              TUBBY_TUBBZ?
              • 4. Re: Saving sql query result as excel file
                783956
                Nice "trick" !!

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

                John.
                • 5. Re: Saving sql query result as excel file
                  Marwim
                  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
                  https://exceldocumenttype.samplecode.oracle.com/
                  Others can be found at Re: How to save a query result and export it to, say excell?

                  Regards
                  Marcus
                  • 6. Re: Saving sql query result as excel file
                    788764
                    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
                      JuanM
                      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:

                      http://tips-oracle-mx.blogspot.com/2010/12/sqlplus-con-salida-xls.html

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

                      Hope this help and not a spam. :P

                      Thanks.

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

                        SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF ;
                        SPOOL C:/MY_TEST_FILE.xls;
                        SELECT * FROM EMP;
                        SPOOL OFF;

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

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

                          Regards
                          Marcus