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
Printable View
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
you cannot, the data is exported in binary format, only the DDL are exported in ASCII
Then how do you transfer data from Oracle to anything else, for example, an Excel spreadsheet?
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
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!
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.
We normally use pipe instead of comma as delimiter since there is unlikely any | in original data.
Many thanks for your help, everyone!