SUPPORT THE SITE WITH A CLICK

Subscribe Rss:

SUPPORT THE SITE WITH A CLICK

Wednesday, March 2, 2011

Export oracle sql output to excel format

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.

5 comments :

  1. This was awesome!! . thanks a lot :)

    ReplyDelete
  2. See some examples here..
    http://shonythomas.blogspot.in/2011/07/export-and-create-excel-file-from.html

    ReplyDelete
  3. Somehow is places all fields values into the single column

    ReplyDelete
  4. I 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