Wednesday, April 23, 2008

Export from Oracle table to CSV file

Sometimes you need to export result of the query to CSV file. Here is a nice example of how to do this kind of operations.


SQL> SET LINESIZE 500 FEEDBACK OFF TRIMSPOOL ON TERMOUT OFF HEAD OFF PAGESIZE 0 TERM OFF
SQL> spool outfile.csv
SQL> select '"'|| column_1 || '",' || column_2 || ',' || column_3 from mytable where [your where statement]
...
...
SQL> spool off
SQL> exit

In this case column_1 data will be surrounded by "" (you may need this if you have , character in field data).

Update: SET LINESIZE determines a maximum number of characters in the line. So if you expect output bigger than 500 lines you must set this to a bigger value.

2 comments:

Louis said...

Apparently, this doesn't work with tables with long and many fields.
Mine truncates after the 6th field.

Viktor Yarmak said...

Louis,

try to increment a line size setting it to bigger value - 5000 for example.

> SET LINESIZE 5000 FEEDBACK OFF TRIMSPOOL ON TERMOUT OFF HEAD OFF PAGESIZE 0 TERM OFF