Wednesday, January 17, 2018

CSV output from Oracle



Prior to 12c R2 , producing CSV ouptut from a table required bit of work . 
With 12c R2 , the process is quick and easy. The good old SQLPlus has got the long awaited option . 

With the SET option  , we should be spooled to a file quicker. 

Here is an example. 

SQL> select * from emp;

ENAME                        ID
-------------------- ----------
Zahir                        10
Hameed                       20
Farook                       30
Basheer                      30


SQL> set markup csv on

SQL> select * from emp;

"ENAME","ID"
"Zahir",10
"Hameed",20
"Farook",30
"Basheer",30


By default , the delimiter for the character values will be enclosed in quotes. 
We can turn it off by the quote off. 

SQL> set markup csv on quote off
SQL> select * from emp;

ENAME,ID
Zahir,10
Hameed,20
Farook,30
Basheer,30



Sometimes , it is beneficial to a different delimiter. 
In the following example , we use '|' as the delimiter . 

SQL> set markup csv on delim '|' quote on
SQL> select * from emp;

"ENAME"|"ID"
"Zahir"|10
"Hameed"|20
"Farook"|30
"Basheer"|30


In this way , the CSV ouput from oracle tables/views is a loooooot easier. 

Comments welcome. 


Update : 
To make the example complete . We need to use spool on/off to produce a file in our directory.  In the example below , I have spool the results to a file emp.csv . We can few other options to not to display ( supress ) the header , query . To make it simpler , I have just simply spooled the file . 

Thanks very much  to my good friend , Soma for pointing out this .  


SQL> spool  emp.csv
SQL> select * from emp;

"ENAME"|"ID"
"Zahir"|10
"Hameed"|20
"Farook"|30
"Basheer"|30


SQL> spool off
SQL> host dir
 Volume in drive D has no label.
 Volume Serial Number is AXCA-3871

 Directory of D:\test

01/17/2018  03:26 PM              .
01/17/2018  03:26 PM              ..
01/17/2018  03:27 PM               123 emp.csv
               1 File(s)            123 bytes
               2 Dir(s)  710,110,654,464 bytes free

SQL> ho type emp.csv
SQL> select * from emp;

"ENAME"|"ID"
"Zahir"|10
"Hameed"|20
"Farook"|30
"Basheer"|30


2 comments:

  1. Good one Zahir! I use: Set markup csv on quote off too! Do you not use spool on and spool off to mark the csv file beginning and ending?

    ReplyDelete
    Replies
    1. Hi Soma -

      You need to spool on and spool off to produce a file in a directory. I will update the blog with that.

      Delete