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!