SQL Plus Spool as CSV

To get a CSV output from SQL Plus you might be inclined to concatenate the SELECT columns together with commas:

SPOOL MyOutputFile.csv

SELECT col1 ||’,’|| col2 ||’,’|| col3 from table;

SPOOL OFF;

This can get quite messy and doesn’t read well.

But you don’t have to do this, if you set COLSEP to “,” you will get a csv file with tab style spacing and you can keep your SQL formatted with a regular SELECT statement.

SET COLSEP ,

You can then easily suck out the whitespace afterwards with a Perl one liner

                perl -pi.bak -e “s/ *,/,/g;s/, */,/g” MyOutputFile.csv

This makes a backup of the file and does in inline replacement, taking out all spaces following or preceeding a comma.

This will ignore spaces embedded in a string, say “xxx,Mr Joe Bloggs,xxxx”.