How to Spool SqlPlus Output to Microsoft Excel Format




It is very easy to export and save output of executed query in xls sheet using toad but how to do it on sql prompt. To do so you need to use some text formatting options and spool file name to save output.

To Spool SQLPLus output to Excel (.xls) file, the trick is to turn On "MARKUP HTML".


SET PAGESIZE 40000

SET FEEDBACK OFF

SET MARKUP HTML ON

SET NUM 24

SPOOL file_name.xls



---- Execute your query



SPOOL OFF

SET MARKUP HTML OFF

SPOOL OFF



Example:

SQL> SET PAGESIZE 40000

SQL> SET FEEDBACK OFF

SQL> SET MARKUP HTML ON

SQL> SET NUM 24

SQL> SPOOL ora_htl_hist.xls

SQL>

SQL>SELECT COUNT * FROM ORA.HTL_HIST;

SQL>

SQL>SPOOL OFF

SQL>SET MARKUP HTML OFF

SQL>SPOOL OFF


A file name ora_htl_hist.xls will be generated in the current directory. This is the best way to export and save sql plus output to xls sheet.

3 comments:

  1. set NUM 24 option is for what? My column names are getting truncated. Shall i increase this limit in NUM 24?

    ReplyDelete
    Replies
    1. Yes Arun increase this value if value is getting truncated. Basically it controls the default width used when displaying numeric values.

      Delete
  2. Really Nice way to get the output in XLS. Thanks a lot for saving a lot of time by simplifying and putting things together.

    ReplyDelete

CONTACT

Name

Email *

Message *