Oracle and more – Amardeep Sidhu

My experiences with technology…

Spool to a .xls (excel) file…

with 12 comments

A small tip, I read on OTN about spooling to a .xls (excel) file:

It goes like this

set feed off markup html on
spool onspool c:\salgrade.xls
select * from salgrade;
spool offset markup html off
spool off

And the xls it makes shows up like:

Sidhu

Written by Sidhu

June 16th, 2007 at 12:14 am

Posted in Oracle Tips, SQL

Tagged with ,

«
»

12 Responses to 'Spool to a .xls (excel) file…'

Subscribe to comments with RSS or TrackBack to 'Spool to a .xls (excel) file…'.

  1. hi
    i am working for a bank and we are using 10g.
    i am very raw at the oracle and have just started teaching myself through a book i have.
    we have many reports devised by our vendor still we need some that r not available.
    so we wanted the data to be exported in xl wherein i could manipulate to data to our need.
    abt this spooling thing. i have copied down ur script and want to test it… but how do i access the shell prompt(do not know unix either)
    thanks for the help
    extend it bit further for me please
    hemu

    hemant

    26 Jun 07 at 4:44 pm

  2. Hemant

    Here what I wrote for spooling to xls file, needs to be run on SQL Prompt not shell. Any other help you need leave a message here.

    Cheers
    Sidhu

    Sidhu

    26 Jun 07 at 8:21 pm

  3. Hi Amardeep,

    This is Ajith,working in kuwait as Oracle Consultant.I would like to get some basic learning materials on shell scripting.if can refer me some good sites or materials i would be very greatful.

    thanks and regards,

    Ajith

    Ajith

    9 Jul 07 at 12:20 pm

  4. Hi,
    I am facing some issues with the spooling method. I m generating an xls using the spool . However in some lines , the lines get CUT into 2 lines in the output spool file , leading to 2 rows in xls instead of one. PLease let me know if you have come across this and have a solution.
    An example of what the problem is

    spool $MONTH_DATA_FILE;

    select data from ap_tc_reports_temp;

    spool off;

    Output

    =================
    C1 C2 C3 C4 C5 C6 C
    7 C8 C9 C10
    ===================

    The “C” is actually C7 , all in one line, but output is in 2 lines.

    Prachi

    14 Aug 07 at 5:22 pm

  5. Prachi

    I doubt about the length of some field in table. May be excel doesn’t support that much length of a column and it takes it to next row. I didn’t face this issue. Will try and update you.

    Sidhu

    Sidhu

    15 Aug 07 at 12:02 am

  6. Hi ,
    I found a solution. It needs the lines to be set to some value . The value i was setting was less.

    Prachi

    16 Aug 07 at 11:32 am

  7. So simple :) I got into too technical things :D

    Sidhu

    Sidhu

    17 Aug 07 at 7:41 am

  8. this scripting is working fine.. but i need some enhancement. my problem is when i am running the script its generating xls file with the some text like “SQL>select * from salgrade ” and header are reprinted for several times after some rows.. so how can i overcome this problem.

    sami

    21 Mar 09 at 12:30 pm

  9. To stop the header from printing again and again you can set pages to some value more than the total number of rows your query is returning.

    Amardeep Sidhu

    21 Mar 09 at 7:38 pm

  10. Is any way to import from “dbms_output” into Excel file?

    Nice tip and thank you for sharing with us

    Best Regards,
    khair

    Kostas Hairopoulos

    29 Aug 09 at 8:22 pm

  11. @Kostas

    What exactly are you trying to print using dbms_output ?

    Amardeep Sidhu

    18 Sep 09 at 10:51 pm

  12. I am running the snapper utility from Tanel Poder and the only option is to output the file and then import as CSV in ms-excel.
    My question is more generic, if there is any option to flush the dbms_output to excel or csv format

    Thank you in advance,
    khair

    Kostas Hairopoulos

    21 Sep 09 at 11:03 pm

Leave a Reply