Export and Create Excel File from Oracle SQL Query Spool Output File

advertisements

_____________________________________________________________________________________________________________________

Most of the time, it is a common requirement to export the SQL*Plus spool output to the excel spreadsheet. Earlier I used to spool the SQL output to a text file and I use convert text data to Excel columns. It requires some additional effort. Please see the demo for simple method to create Excel file from sql spool output.

sqlplus scott/tiger

set feedback off

set markup html on spool on

alter session set nls_date_format='mm-dd-yyyy';

spool objects.xls

select object_name,object_type,created from user_objects ;

spool off

set markup html off spool off

SQL> set feedback off

SQL> set markup html on spool on

SQL> alter session set nls_date_format='mm-dd-yyyy'

<br>

  2  ;

<br>

SQL&gt; spool objects.xls

<br>

SQL&gt; select object_name,object_type,created from user_objects ;

<br>

<p>

<table border='1' width='90%' align='center' summary='Script output'>

<tr>

<th scope="col">

OBJECT_NAME

</th>

<th scope="col">

OBJECT_TYPE

</th>

<th scope="col">

CREATED

</th>

</tr>

<tr>

<td>

PROCEMP1

.. .. ..

<p>

SQL&gt; spool off

<br>

SQL&gt; set markup html off spool off

<br>

<br>

SQL>

Open the objects.xls file in excel. The output will be as below.

Is it simple? J

_____________________________________________________________________________________________________________________

Website Stats