An Oracle blog – Amardeep Sidhu

June 16, 2007

Spool to a .xls (excel) file…

Filed under: Oracle Tips,SQL — Sidhu @ 12:14 am
Tags: ,

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

14 Comments »

  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

    Comment by hemant — June 26, 2007 @ 4:44 pm | Reply

  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

    Comment by Sidhu — June 26, 2007 @ 8:21 pm | Reply

  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

    Comment by Ajith — July 9, 2007 @ 12:20 pm | Reply

  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.

    Comment by Prachi — August 14, 2007 @ 5:22 pm | Reply

  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

    Comment by Sidhu — August 15, 2007 @ 12:02 am | Reply

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

    Comment by Prachi — August 16, 2007 @ 11:32 am | Reply

  7. So simple 🙂 I got into too technical things 😀

    Sidhu

    Comment by Sidhu — August 17, 2007 @ 7:41 am | Reply

  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.

    Comment by sami — March 21, 2009 @ 12:30 pm | Reply

  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.

    Comment by Amardeep Sidhu — March 21, 2009 @ 7:38 pm | Reply

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

    Nice tip and thank you for sharing with us

    Best Regards,
    khair

    Comment by Kostas Hairopoulos — August 29, 2009 @ 8:22 pm | Reply

  11. @Kostas

    What exactly are you trying to print using dbms_output ?

    Comment by Amardeep Sidhu — September 18, 2009 @ 10:51 pm | Reply

  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

    Comment by Kostas Hairopoulos — September 21, 2009 @ 11:03 pm | Reply

  13. Hi Sidhu,
    I have created a shell script which needs to get some customer data from a .dat file. However, when i’m trying to export the data to xls file, its not reflecting in the file. Although, the timestamp of last modification of the file is getting updated.
    Can you please help me in this ?

    Comment by Ayush — June 14, 2011 @ 7:11 pm | Reply

  14. Ayush,

    I am not sure if i exactly got what you are trying to achieve ?

    Could you please come with some more details.

    Sidhu

    Comment by Sidhu — July 20, 2011 @ 10:31 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Theme: Rubric. Get a free blog at WordPress.com

%d bloggers like this: