Oracle - How to convert table data into CSV file

 

In Oracle, below are the three ways can use to extract table data to CSV file using SQLPlus SPOOL options.

1) Using colsep option like SET COLSEP ","

2) Using concatenation of  column values with delimiter. 

3) Using the MARKUP CSV ON option.

On the above three options MARKUP option is more efficient compared to others. Here is an shell script example code to convert table data to CSV file.

echo "Starting to convert data to csv........"

sqlplus -s $DBDETAILS << EOF > /dev/null

SET MARKUP CSV ON DELIMITER , QUOTE OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 32000
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0

SPOOL "result.csv"

select 'customer_id,customer_name,city' from dual;
select customer_id,customer_name,city from customers;

SPOOL OFF 
EXIT 
EOF

echo "Completed csv conversion."

SQLPlus reference link https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#SQPUG060.