Re: SQL*Plus reporting question

d_newsham_at_hotmail.com (D Newsham) wrote in message news:<c883e8dd.0205211008.3b605ef1_at_posting.google.com>...
>
> I have written a query to produce a csv file from SQL*Plus. I also
> need the headers to come out as a single line, comma separated. Can
> this be done?
>
> So far I have discovered that I can turn the headers off, make them
> come out in one single string, or be separated by space with an
> underline underneath. I really need the headers to come out in a
> single, comma separated line so my client can import it into Excel
> without having to type in all of the column headers.
>
> Here is what I have so far (actual file contains about 60 columns):
>
> set heading off pagesize 0 linesize 2000 feedback off
>
> spool testfile
> SELECT
>
> column_1||','||
> column_2||','||
> column_3||','||
>
> FROM test_table
> order by column_1,column_2,column_3
> /
> spool off

I would say the simplest thing to do is take the commas out of your query and use colsep to set the column separator to a comma. Then set the page size as big as your result set, this may not always be possible but is usually doable for the number of rows wanted in Excel.

I use a script like this (rpt2csv.sql):

--
-- save settings
--
store set set_save.sql replace
--
-- set environment
--
set termout off
set verify off
set feedback off
set pagesize 1000
set linesize 120
set trimspool on
--
-- set output .csv extension
--
column output new_value output noprint
select '&1'||'.csv' output from dual;
set colsep ','
--
-- spool query
--
spool &output
select ename, job, sal from emp;
spool off
--
-- restore settings
--
@set_save
--

Then run it like this:

martin_at_BUB> @rpt2csv sheet1
martin_at_BUB> host type sheet1.csv

ENAME     ,JOB      ,       SAL
----------,---------,----------
SMITH     ,CLERK    ,       800
ALLEN     ,SALESMAN ,      1600
WARD      ,SALESMAN ,      1250
JONES     ,MANAGER  ,      2975
MARTIN    ,SALESMAN ,      1250
BLAKE     ,MANAGER  ,      2850
CLARK     ,MANAGER  ,      2450
SCOTT     ,ANALYST  ,      3000
KING      ,PRESIDENT,      5000
TURNER    ,SALESMAN ,      1500
ADAMS     ,CLERK    ,      1100
JAMES     ,CLERK    ,       950
FORD      ,ANALYST  ,      3000
MILLER    ,CLERK    ,      1300

      
The problems you'll probably hit is when you have commas in the
columns or leading zeros which Excel insists on trimming from your
csvs you no matter what you do. In these case, which is most I set
colsep to a tab and generate a .txt file which gets formatted
correctly when you accept the defaults in the text import wizard.

If you need to set colsep to a tab:

column sep new_value sep noprint
select chr(9) sep from dual;
set colsep sep