Export data to excel

Latest posts by Cyrille Modiano (see all)

who never needed to export data to excel from sqlplus ? everybody had one day needed to do that.
Sqlplus support the HTML markup which provides an excellent result once opened in excel.
With the HTML markup, sqlplus create a table with all columns from your query including the name of your columns as table header.
Here is a small exemple:

spool myxlsfile.xls
SET MARKUP HTML ON ENTMAP ON PREFORMAT OFF ;
select 	to_char(end_time,'hh24:mi:ss') as sample_time,
		value
from 	v$sysmetric_history
where 	end_time between sysdate - interval '1' hour and sysdate
and 	group_id = 2
and 	metric_name = 'Host CPU Utilization (%)'
order by metric_name, end_time;
spool off

my output file has already the xls extension, I just have to open it, here is how it looks like:

export data to excel

from there it is easy to generate a chart for example:

host utilization percent

Thanks for reading.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.