Export To Ascii Text

[maxhugen]

Can anyone advise me how to export the data from a table or view, to a simple comma-delimited ascii text file please?

MTIA, Max Hugen
maxhugen@hugen.com.au

[pando]

you cannot, the data is exported in binary format, only the DDL are exported in ASCII

[maxhugen]

Then how do you transfer data from Oracle to anything else, for example, an Excel spreadsheet?

[pando]

I think excel can connect to Oracle using ODBC I am not sure but you could load data this way as suggested by Tom Kyte

http://osi.oracle.com/~tkyte/flat/index.html

[maxhugen]

Thanks for the link to Tom's page... I use ODBC already, but I wanted an unlinked ascii file to be generated from Oracle itself.

I was actually going to use it to save a merge data file to a user's pc from an Oracle db sitting on the Internet, so they could do a mail merge using MS Word.

I think Tom's article might do the trick. Many thanks, have a good day!

[molonede]

Export has multiple meanings in Oracle. The actual Export utility will only export binary files that oracle can read to import back into a database. However you can use sqlplus and concatentation operators to export to an ascii text file.
Warning watch out for columns with embedded commas in data like a name field example.

"lastname, firstname"


sqlplus -s /nolog
connect user@sid/password
spool /tmp/comma.out
set heading off;
set pagesize 0;
set feedback off;
set echo off;
set pause off;
set termout off;
select col1||','||col2||','||col3 from tableA;
spool off
exit

the file /tmp/comma.out now contains your comma delimeted ascii text file.

[jt]

We normally use pipe instead of comma as delimiter since there is unlikely any | in original data.

[maxhugen]

Many thanks for your help, everyone!