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.
Hi Mark,
ReplyDeleteI 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
How can i specify a storage path i.e for saving the .xls in a specific directory?
ReplyDeleteif I have 2 query, is it possible to export the 2 result to 2 different sheet of same excel file.
ReplyDeletecan you export the results only without the sql.
ReplyDeleteThanks for explaining step by step. It really helps to spool sqlplus output in excel format.
ReplyDeleteNice article ..very very helpful
ReplyDelete