Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Monday, July 09, 2007

Extracting Data to Excel using only SQL*Plus

Lately I had to quickly extract some data from Oracle DB to Excel. There are a lot of tools that can do that, ex. Toad or SQL Developer, but I had only access to SQL*Plus. Additionally I was on Windows machine and database was in UTF8. And extracted data was also using many different languages.
Here are steps I used to archive my goal:
  1. start command line (CMD)
  2. set NLS_LANG variable
    set NLS_LANG=.UTF8
  3. start sqlplus and log to database
    sqlplus user/pwd@db
  4. set long line size and avoid paging
    SQL> set linesize 4000
    SQL> set pagesize 0
  5. set markup html in sqlplus
    SQL> set markup html on spool on

    It is important to use spool on clause, as it adds html header to spool file when spooling is started
  6. start spooling
    SQL> spool a.html
  7. run your query
  8. turn off spooling
    SQL> spool off
  9. open file in Excel
I've tested this procedure on Oracle 10g R2 (10.2.0.1 and 10.2.0.3) and Excel 2003

Cheers, Paweł

11 comments:

Noons said...

one thing you can try as well:
define a dsn for ODBC in the client to the Oracle data provider and your database alias, setup oracle client on that pc and then run the query directly from inside Excel, using a SQL statement from inside the spreadsheet.
works quite well.

Paweł Barut said...

ODBC is also an solution. But in my case I could not force it to work correctly with UTF8 data. I had data in many languages and lot of data get corrupted that way.

Susanta Chattopadhyay said...

Pawel,
This trick is great as I have an web application which handles many languages and I could not get output which is readable from queries. Thanks for the good tip.

- Susanta

Rao said...

Pawel,

It is really working well. But is there any way we can remove this initial sql statement and add column headings to excel?

Your help is really appreciated.

rao said...

How to add a column header to this html output?

Anonymous said...

Can you create and HTML with mutiple worksheet with this method, if so how???

Admin said...

Now how do you add another sheet ?

Regards,

Sanjay

Paweł Barut said...

Hi Sanjay and Anonymous,

This is not possible to add another sheet with this method. This is just to dump data fast to excel.
To have fully featured XML, that is readable by Excel try this:
1. Save small excel file as .xml file
2. analyze .xml that you get. It will contain tags like Table, Row, Cell, Data, and what you are interested in "ss:Worksheet"

But to get this done this way, SQL*Plus is not enough. You will have to do some more codding...

BR/Paweł

Alexander Murdoch said...

Hello.

First of all, this a nice and simple solution. I'm using it every day.

Rao.

You can add the heading removing obviating the command "set pagesize 0"

Also, you can edit the file from the console to remove all the extra information you don't want.

Thanks.
Alex.

Alexander Murdoch said...

Sorry...there was a small mistake on my previous message.

IF you remove pagesize statement, you will have your headings every 10 records. Since we want to have a heading just on the first line, i recommend you to a value grater than the records returned in case you know that value, or to a high value like 99999999.

Thanks.
Alex.

Shony said...

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

 

Copyright © Paweł Barut
Printing from DOS to USB Printer