Export to Excel
May 11th, 2007
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 
very neat indeed
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.
I prefer
set pages 0 head on emb on newp noneset pages 0 head on emb on newp none
somehow does not produce headers for me
Exactly!
Simply great!
Karl
robbert, I am sorry about this, you are right, pages 0 is not working for HTML-Markup
I hope I can find some alternative …
Laurent, this is great. I use this to produce html reports.
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
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
put the query in a file and use @file
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
mbpenaloza@yahoo.es
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.
I will have a look
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 < and > and not < and >
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.
you can turn off screen output in your script. Only spooling to a file is usually faster than spooling to the screen (Terminal)
set termout off
Excellent !
For a bonus point !
How can we add a company logo dynamically top the top of the file ?
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!!
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>
replace :y by your procedure out variable or whatever you like
you could test it in sqlplus with
VAR Y CLOB
SET AUTOP ON
Laurent, i have no words to describe, the helpfully this it!!
thanks a lot!!
(now, i can not erase the smile in my face )
Glad to help you
Wow, this also give column headings in bold ! Great stuff!
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.
really? could you copy paste your input/output and provide version?
Thanks Laurent… I could manage to do it. Its its really amazing. U r are a genious. Thanks a lot. Thanks..
:)
Thanks, but it still will transform “007″ in 7 in Excel …
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…
Maybe XML format or otherwise InterMedia
Hi Laurent, I wish to execute those steps automatically every day at 21:00 hours. How can I do that? Please help me! Thanks!!
Use your job scheduling system! cronjob in unix, scheduled tasks in Windows, then run your batch job
Thanks a lot Laurent!!
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
for instance create define variables …
file.txt:
def x=1
def y=foo
def z=2009-01-01
ok?
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
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
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
set ver off
Laurent,
very need. Thank u very much!
Hi Laurent,
how do we write/spool into excel with multiple sheets?
do u have some example?
thanks so much
if you have a recent version of Excel, you could use XML. But no I do not have an example handy, sorry