Re: export table to a csv file

To do a very primitive .CSV file you can use SQLPlus and spool out a file using the following:

Do a select with a union. First part of the union concatinate first a couple of spaces then each heading text item with a comma concatinated in between each heading text item of what you want in the header: (Note: the leading spaces in the first part of the union just make the headings sort to the top.)

SET TERMOUT OFF
SET HEADING OFF
SET SHOWMODE OFF
SET LINESIZE 2000
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF
SET TIMING OFF
SET NEWPAGE 1
SET WRAP ON
SET LONG 32767
SET ARRAYSIZE 1
SET HEADING OFF spool c:\temp\csvfile.csv

  select

'||'heading1'||','||'heading2'||','||'heading3'||','||'heading4'||','||'heading5'||','||'heading5'||','||'heading6'||','||'null'

  from dual
union
  select

column1||','||column2||','||column1||','||column3||','||column4||','||column5||','||column6||','||null

  from your_table
/

Open the file with Excel using a comma as your delimiter and check it out.

Of course, it is much nicer to have a great tool like Oracle Discoverer to do the deed. But I had to pull something off like above when I needed a .CSV file from a Oracle Form web app and it worked like a charm and auto displayed in Excel thru Netscape. rq

budgielover_at_yahoo.com wrote:

> hi
>
> I need to export a table to csv file including the header in command-
> line. I'm currently using personal oracle.
>
> thanks
>
> Joanne
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Dec 09 1999 - 00:06:28 CET