Re: Outputting a table/recordset to a csv

Aidan Whitehall wrote:
> Forgive the beginner's Q... trawled Google groups and got this far (below),
> but it doesn't work. I'm just trying to output a recordset to a text file.
> Ideally, I'd like the whole table but the examples that turned up specified
> the column names.
>
> Anyone care to point out the stupid mistake(s) I'm making? I've no idea what
> the first seven lines do, BTW. Thanks.
>
> set space 0;
> set feedback off;
> set pagesize 0;
> set heading off;
> set linesize 1000;
> set echo off;
> set termout off;
> spool "C:\temp.csv";
>
> select st_site_id || ',' || ow_owner_id || ',' || hsg_assessment_date
> from hsg146;
>
> spool off;
>
> --
> Aidan Whitehall <aidanwhitehall_at_fairbanks.co.uk>
> Macromedia ColdFusion Developer
> Fairbanks Environmental +44 (0)1695 51775
>
>

What error are you getting? If any of the columns are numeric, you need to make them something like to_char(st_site_id). You don't need the quotes around your spool file (unless you have embedded spaces). You don't need the semicolons on the set statements (though they won't cause an error) and, in fact, they can be on one line as:

set space 0 feedback off pagesize 0 heading off linesize 1000 echo off

You might also add 'set trimspool on' which will take the trailing spaces off each output line.

Here's what the set commands mean:

space 0 = zero spaces between columns (not really required) feedback off = suppresses the 'n rows' feedback line pagesize 0 = turns off page breaks
heading off = turns off column headings
linesize 1000 = each line is 1000 bytes long echo off = suppresses lines from command files termout off = suppresses output from command files Received on Mon Oct 21 2002 - 12:16:40 CDT