Monday, May 13, 2013

Oracle export table data to CSV format.

There are many ways to export Oracle table data to CSV format.

Easiest way is simple SQL spool clause:

With following sql spool you can get txt file that contains data in CSV format (with strings (columns 2,3 and 5) in single quotation marks):
SQL> spool testdata.csv
SQL> SELECT column1||',"'||column2||'","'||column3||'",'||column4||',"'||column5||'"'FROM testtable1;
SQL> spool off;



Example:
SQL> desc test_schema.test_table_1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                               VARCHAR2(20)





SQL> spool test.csv
SQL> SELECT col1||','''||col2||'''' FROM test_schema.test_table_1
SQL> spool off;


You can also use Oracle UTL_FILE package to get data in CSV format more info about that from asktom.oracle.com:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:88212348059

No comments:

Post a Comment