Usually for exporting oraclce sql output to any formats (text, pdf, excel, xml), we will go for toad or any other tools.But when we want to fetch the sql output from server, this is not an easy one.Some cases firewall issue will break the toad to get connected from server.
For exporting the oracle output to excel, we can use the following command in terminal
SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF ;
SPOOL /home/$user/sqldata.xls;
SELECT * FROM SITES.ARTICLES;
SPOOL OFF;
SPOOL :Stores query results in a file, or optionally sends the file to a printer
Step 1:
Will convert will remove all the spaces and convert all characters, symbols into ascii
Step 2:
Path to store the sql output as excel
Step 3:
Sql query to fetch
Step 4:
Swtiching off the spool
So its too simple to convert the sql results to excel format in oracle.
Subscribe to:
Post Comments
(
Atom
)
excellent :)
ReplyDeleteThis was awesome!! . thanks a lot :)
ReplyDeleteSee some examples here..
ReplyDeletehttp://shonythomas.blogspot.in/2011/07/export-and-create-excel-file-from.html
Somehow is places all fields values into the single column
ReplyDeleteI also want to spool data to excel file and it is doing fine. But, is there an option or additional command or added solution to prevent from opening the excel file with the warning message? The message is "The file you are trying to open, "salgrade.xls" is in different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"
ReplyDelete