Spool SqlPlus Output to Microsoft Excel Format


Spool SqlPlus Output to Microsoft Excel Format


SET PAGESIZE 50000
SET FEEDBACK OFF
SET MARKUP HTML ON SPOOL ON
SET NUM 24
SPOOL file_name.xls

---- Execute your query

SPOOL OFF
SET MARKUP HTML OFF SPOOL OFF



Example:

SET PAGESIZE 50000
SET FEED OFF MARKUP HTML ON SPOOL ON
SET NUM 24
SPOOL EMP_DATA.xls

Select EMPNO, ENAME, HIREDATE, SAL
From Scott.EMP
/

[[ The output from above query will be displayed ..... ]]

SPOOL OFF
SET MARKUP HTML OFF SPOOL OFF



NOTE:

The file will contain the SQL as well. Save the file as Your_File_name.xlsx

The file should be in the Current Working Directory.

Comments

  1. Hi Mark,

    I have been using this and this works great. Do you know how can APPEND to the excel file I created using the above the script.

    I used the same script as above.. except I did SPOOL C:\xyz.xls APPEND and it didn't work

    ReplyDelete
  2. How can i specify a storage path i.e for saving the .xls in a specific directory?

    ReplyDelete
  3. if I have 2 query, is it possible to export the 2 result to 2 different sheet of same excel file.

    ReplyDelete
  4. can you export the results only without the sql.

    ReplyDelete
  5. Thanks for explaining step by step. It really helps to spool sqlplus output in excel format.

    ReplyDelete
  6. Nice article ..very very helpful

    ReplyDelete

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

How to check fragmentation in MySQL tables

How to Drop or Remove or Decommission a Database in Oracle