Software Engineer Thoughts: Extracting Data to Excel using only SQL*Plus

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:

start command line (CMD)
set NLS_LANG variable
```
set NLS_LANG=.UTF8
```
start sqlplus and log to database
```
sqlplus user/pwd@db
```

set long line size and avoid paging

SQL> set linesize 4000
SQL> set pagesize 0

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
start spooling
```
SQL&gt; spool a.html
```
run your query
turn off spooling
```
SQL&gt; spool off
```
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.; 10 July, 2007 01:21
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.; 10 July, 2007 07:59
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; 11 September, 2007 23:29
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.; 30 June, 2008 20:58
rao said...: How to add a column header to this html output?; 01 July, 2008 15:20
Anonymous said...: Can you create and HTML with mutiple worksheet with this method, if so how???; 23 January, 2009 20:35
Sänjay said...: Now how do you add another sheet ?

Regards,

Sanjay; 17 April, 2009 21:14
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ł; 17 April, 2009 22:21
Alex 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.; 15 May, 2009 20:40
Alex 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.; 15 May, 2009 21:39
Shony said...: See some examples here.. http://shonythomas.blogspot.in/2011/07/export-and-create-excel-file-from.html; 22 February, 2012 03:23