Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2012
    Posts
    2

    Unanswered: Export SQL Plus calling a SQL Script to Excel

    Hello All!

    I am trying to export results from SQL Plus to Excel and using the following super simple code in my SQL Script:

    set feedback off markup html on spool on
    alter session set nls_date_format='YYYY-MM-DD';

    set PAGESIZE 50000
    set LINESIZE 10000

    set termout off

    spool myresults.xls

    select fields from tablename;

    spool off
    set markup html off

    The results are exported into an Excel worksheet, but when opening the file, receive the following error:
    "The file you are trying to open, 'myresults.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

    If I click yes, the file opens fine. I save the file to a different location and it appears to be an .xls file, but since it is created using the "markup html on" set command in my SQL, I wonder if there is HTML in the doc?

    Any help to be able to create the Excel file this easy way and be able to open it without an error message?

    I do not have permissions to change my users' registry settings, etc.

    Thanks!
    Sunny

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >spool myresults.xls
    simply naming the *xls does NOT make it an Excel file.
    EXCEL using ODBC can connect directly to Oracle RDBMS & then issue SQL;
    without any intermediate data file.

    Search results for "excel" - Oracle FAQ
    https://forums.oracle.com/forums/thr...174552#9360007
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2012
    Posts
    2
    Thank you for your response.

    The requirement is to create an Excel dump from Oracle. To use Excel to query Oracle is not an option in this situation.

    So I am still on the hunt for a solution.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The requirement is to create an Excel dump from Oracle.
    Oracle has no capability to write a pure Excel spreadsheet file; which in a binary format.
    Oracle can write plain text files either as Comma Separated Value or XML file; which Excel can import.
    It is up to you to ensure the file content is in a format that Excel accepts.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    should have been

    spool myresults.html

    which excel will open with no problems.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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