Export to Excel

One more neat solution from Michaels about exporting the data to Excel in this otn post


set feed off markup html on spool on
alter session set nls_date_format='YYYY-MM-DD';
spool emp.xls
select * from emp;
spool off
set markup html off spool off

And it perfectly opens in Excel. No hassle with separator, no time lost in defining the column length, no bizarre xml format. Plain html, that is cool!

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980-12-17 800   20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975   20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850   30
7782 CLARK MANAGER 7839 1981-06-09 2450   10
7788 SCOTT ANALYST 7566 1987-04-19 3000   20
7839 KING PRESIDENT   1981-11-17 5000   10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 1100   20
7900 JAMES CLERK 7698 1981-12-03 950   30
7902 FORD ANALYST 7566 1981-12-03 3000   20
7934 MILLER CLERK 7782 1982-01-23 1300   10

Simply great 😛

54 thoughts on “Export to Excel”

  2. Robbert Michel

    You might want to add something like set pages 50000
    (unfortunately the max number)
    to that, unless you want the header to repeat every few lines.

  4. Robbert Michel

    set pages 0 head on emb on newp none
    somehow does not produce headers for me 🙂

  8. Pingback: Exportar datos a Excel, de una forma muy simple (Sql*Plus) « Oracle y yo….

  9. ranjit

    Hi Laurent,

    It is really amazing, I am able to get the out put but i am able to execute the entire sql code as a single script in plsql.

    Could you please help me out on this.

    Also after running the sql commands one by one, i am getting the select query which comes after spool, in the excel. I dont want this, please help us how can we avoid the query in output ecel file.

    the sql i am using…

    set feed off markup html on spool on
    set pages 0 head on emb on newp none
    alter session set nls_date_format=’YYYY-MM-DD’;
    spool emp.xls
    select * from emp;
    spool off
    set markup html off spool off

  10. ranjit

    Hi Laurent,

    It is really amazing, I am able to get the out put but i am not able to execute the entire sql code as a single script in plsql.

    Could you please help me out on this.

    Also after running the sql commands one by one, i am getting the select query which comes after spool, in the excel. I dont want this, please help us how can we avoid the query in output ecel file.

    the sql i am using…

    set feed off markup html on spool on
    set pages 0 head on emb on newp none
    alter session set nls_date_format=’YYYY-MM-DD’;
    spool emp.xls
    select * from emp;
    spool off
    set markup html off spool off

  12. Peñaloza Marcelo

    Hi Laurent

    I live in Ecuador in South America. Work in Oracle 10g 5 years ago and I am very interest in learn more of Oracle. Work in Comercial company that buys and sells merchandise. I am Software developer in Oracle forms 10g.

    Tanks so much for your information. If you give me your web page it would be for my helpful.

    Excuses by the misspelling, my English is not very good although I try to improve it every day. In my country the official language is the Spanish.

    Many blessings

    [email protected]

  13. Kelly

    Hi Laurent,

    Thanks for this great tip. I’m having one issue though – I have one column that is numeric and about halfway through the xls file, the values in this column are stored as text not numbers, which is very inconvenient. I know I can get the values of these cells and fix it manually, but is there a way that you know of to fix this in the spooling process? I do over 20 reports monthly that are then forwarded to a 3rd party who uses the data, which is why I’m not interested in scanning all the number columns to check for problems.

    Thanks.

  15. Laurent Schneider Post author

    how did you figure out the values are stored as text? do you have a reproducible example?

    be careful with my blog, pasting code in comments is a pain, you need to use &lt; and &gt; and not < and > 👿

  16. salil

    thanks,for providing wonderfull solution,
    i have a problem when the record is very large then it take lot of time, how to speed up this process.

  18. Anonymous

    Excellent !

    For a bonus point !

    How can we add a company logo dynamically top the top of the file ?

  20. Fernando

    Hi, thanks for that code.

    but, i have a question from someone without knowledge.
    if exists, it’s possible to put the result of that report on a procedure, then call it from other environment, as php or asp by example, and show the html in that window.. or put it the @file in other machine or export that string…
    i’m still looking on how do it… and i have no more ideas…

    thanks for all and sorry about my English… cause sucks!!

  21. Laurent Schneider Post author

    Hi Fernando,
    You cannot mix PLSQL and SQLPLUS capabilities. You could however do it yourself


    begin
    :y:='<table><tr><th align=right>EMPNO</th><th>ENAME</th></tr>’;
    for f in (select empno,ename from emp) loop
    :y:=:y||
    ‘<tr><td align=right>’||
    f.empno||
    ‘</td><td>’||
    f.ename||
    ‘</td></tr>’;
    end loop;
    :y:=:y||'</table>’;
    end;
    /
    <table>
    <tr><th align=right>EMPNO</th><th>ENAME</th></tr>
    <tr><td align=right>7369</td><td>SMITH</td></tr>
    <tr><td align=right>7499</td><td>ALLEN</td></tr>
    <tr><td align=right>7521</td><td>WARD</td></tr>
    <tr><td align=right>7566</td><td>JONES</td></tr>
    <tr><td align=right>7654</td><td>MARTIN</td></tr>
    <tr><td align=right>7698</td><td>BLAKE</td></tr>
    <tr><td align=right>7782</td><td>CLARK</td></tr>
    <tr><td align=right>7788</td><td>SCOTT</td></tr>
    <tr><td align=right>7839</td><td>KING</td></tr>
    <tr><td align=right>7844</td><td>TURNER</td></tr>
    <tr><td align=right>7876</td><td>ADAMS</td></tr>
    <tr><td align=right>7900</td><td>JAMES</td></tr>
    <tr><td align=right>7902</td><td>FORD</td></tr>
    <tr><td align=right>7934</td><td>MILLER</td></tr>
    </table>

  23. Fernando

    Laurent, i have no words to describe, the helpfully this it!!
    thanks a lot!!

    (now, i can not erase the smile in my face )

  26. Charles

    Hi Laurent, Am a little bit poor in oracle… Can u please tell how to execute this query in the sqlplus. I am trying to run it and it gives me “missing or invalid option” error.

    Please help me out.

  28. Charles

    Thanks Laurent… I could manage to do it. Its its really amazing. U r are a genious. Thanks a lot. Thanks.. 🙂 🙂

  30. marycarmen

    Hi Laurent, i’m from Peru, South America and i wonder to know if it’s possible to export to Excel but in a specific sheet and in a specific place of this sheet. And other question is if it can be automatic, generate reports automatically. Thanks a lot and your page it’s really cool…

  32. Hedras

    Hi Laurent, I wish to execute those steps automatically every day at 21:00 hours. How can I do that? Please help me! Thanks!!

  33. Laurent Schneider Post author

    Use your job scheduling system! cronjob in unix, scheduled tasks in Windows, then run your batch job
    (
    echo set pages 50000 lin 32000 trims on
    echo select * from emp
    echo /
    ) |sqlplus -s -M "html on spool on" -L scott/tiger > f.xls

  35. maya

    Hi Launrent.
    This very neat and simple script had saved my day 🙂
    rreally happy.
    Do u have any tips in this blog on how to send variable to sqlplus from txt file.. letsay in test.txt i have series of variables, i parse it using batch command, but how do i send this value to sql plus?
    lemme know if there’s a simple command to do this…
    Thanks a lot 🙂

  37. maya

    thanks laurent.
    do u mind to explain more or some sample..
    im not sure how to apply that on my case.

    my batch file parse the txt file :-
    For /F “tokens=1,2” %%a in (Tools.txt) Do (
    i wanted to call the sql from here,
    send the value and do the query in this Do loop
    set ORACLE_HOME=c:\Oracle\Ora92
    set SQLFile=c:\CopyToolLog\query.sql
    sqlplus /nolog @%SQLFile%
    )

    the query.sql contains :-
    connect ultro/ultro@ult
    SET LINESIZE 150
    SET PAGESIZE 150

    set feed off markup html on spool on
    alter session set nls_date_format=’YYYY-MM-DD’;

    column dttm new_value v_dttm noprint;
    select to_char(sysdate-1,’YYYYMMDD’) dttm from dual;

    spool \\LogFile\&v_dttm\MapTrans15_&v_dttm..xls

    select blabla

  38. Laurent Schneider Post author

    something like this :


    set ORACLE_HOME=c:/Oracle/Ora92
    set SQLFile=c:/CopyToolLog/query.sql
    set tmpFile=c:/temp/tmp.sql
    for ... %%a ... do (
    echo def a=%A > %tmpFile%
    echo def b=%B >> %tmpFile%
    ...
    type %SQLFILE% >> %tmpFile%
    ...
    sqlplus /nolog @%SQLFile%
    ...
    )

    and in your sqlfile you get

    select blabla from ... where ... = &a' and ... = '&b'

    HTH
    Laurent

  39. maya

    Laurent, TQ.
    this helped.

    I have another thing to ask,
    in my xls spool file, it contains the variable old and new printed before the header… how do i remove this? eg:
    old 5: and to_char(mt.trans_dat ……
    new 5: ,…..

    thanks in advance

  43. maya

    Hi Laurent,
    how do we write/spool into excel with multiple sheets?
    do u have some example?

    thanks so much

  46. Prince

    Hi Laurent,

    I am getting the exported data as each row in single cell of excel.
    for
    e.g: if there are 5 rows to be exported; each row is exported in single cell of excel.

    here is the code I am using:

    set feed off markup html on spool on;
    set linesize 1000;
    spool C:\temptest.xls;

    select * from temp;

    spool off;
    set markup html off spool off;

    Please suggest on this.

  47. gaur

    hi,
    thanks for the nice solution laurent,
    now i have a different problem,
    I have some numeric data field having 16 digit length.
    So when I am exporting data in the excel it is truncating the last digit(i think this is because of the 15 digit limitation in the excel file.)
    can you please suggest some solution for this.
    thanks .

  49. carina

    Can i export the values only? not including the headers?
    The headers keep on appearing by how many lines in my excel file.

    SET TIMING ON

    SET TERM OFF
    SET VERIFY OFF
    SET FEEDBACK OFF
    SET PAGESIZE 99999
    SET MARKUP HTML ON SPOOL ON HEAD ” ” –
    BODY “TEXT=’#000000′” –
    TABLE “WIDTH=’100%’ BORDER=’1′”

    VAR SPOOLSREASON VARCHAR2(150)
    COLUMN SPOOLSREASON NEW_VALUE SPOOLSREASON
    SELECT ‘C:\Temp\Car\TextBlastReport_Text1’||’.XLS’ AS SPOOLSREASON FROM DUAL;

    SPOOL &SPOOLSREASON

    select * from table;

    SPOOL OFF

    EXIT

Comments are closed.