Spooling output from SQL*Plus to Excel
819158Oct 26 2011 — edited Oct 27 2011I am spooling output from SQL*Plus to Excel. The code that I am using is at the bottom. I want all of the white spaces to be trimmed when I open up the spooled file in Excel. How can I ensure this?
I was reading about the SET TRIM command, but it says that it does not affect spooled output. (http://ss64.com/ora/syntax-sqlplus-set.html)
Is there a way to NOT display the white spaces at the beginning and end of a column in SQL*Plus? In other words, if I pull one column from one record, and that one piece of data is only 3 characters in length, I want the column width to also be 3 characters in length instead of spanning 40 characters across.
Information about the columns for your troubleshooting purposes
IDNUMBER is NUMBER(12)
FIRST is VARCHAR2(40)
LAST is VARCHAR2(60)
Code
SET LINESIZE 100
SET PAGESIZE 0
SET MARKUP HTML PREFORMAT ON
set colsep ,
spool c:\prospects.csv
select '"Prospect ID"', '"First"', '"Last"' from dual;
select to_char(p.idnumber), p.first, p.last from prospect_view p where idnumber = 164515;
spool off