csv output format from sqlplus run in a Unix script

Question

I need to format csv output file from a sqlplus query. The char and varchar output fields come out in the max column size in the table, which means a lot of columns have extra blanks, plus the comma seperator. How can I get rid of the extra blanks in my csv file? Also how can I get "" around the numeric fields?

Answer 1

You can get rid of leading/trailing spaces by using trim function:

http://www.adp-gmbh.ch/ora/sql/trim.html

SELECT TRIM(col) FROM table;

Not sure why you are getting quotes around numeric fields : probably something to do with the way you are concating columns together ?

As D.Shawley asked - it would help if you could post some example SQL you are using to generate your CSV here.

Answer 2

You'll have to concat the fields together, and add the quotes yourself. Also, don't forget to escape any double quotes in your data. The double quotes aren't strictly necessary unless your data contains commas or double quotes, but it doesn't hurt to add them.

SELECT '"' || numcol || '",',
       '"' || REPLACE(strcol0, '"', '""') || '",',
       '"' || REPLACE(strcol1, '"', '""') || '"'
  FROM some_table;

Before you run this you'll probably want to turn headings and feedback off, then spool to a file.

SQL> set heading off
SQL> set feedback off
SQL> set pagesize 0
SQL> spool output.csv
SQL> SELECT ... FROM ...;
SQL> spool off