Export To Ascii Text
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Export To Ascii Text

  1. #1
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44

    Question

    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
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you cannot, the data is exported in binary format, only the DDL are exported in ASCII

  3. #3
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44
    Then how do you transfer data from Oracle to anything else, for example, an Excel spreadsheet?
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  5. #5
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44

    Thumbs up Thank You!

    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!
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au

  6. #6
    Join Date
    Dec 1999
    Location
    Cincinnati, Ohio USA
    Posts
    99
    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.
    Doug

  7. #7
    Join Date
    Oct 2000
    Posts
    76
    We normally use pipe instead of comma as delimiter since there is unlikely any | in original data.
    J.T.

  8. #8
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44

    Smile

    Many thanks for your help, everyone!
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width