Spool to a .xls (excel) file…

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

15 thoughts on “Spool to a .xls (excel) file…

  1. hemant

    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

    Reply
  2. Sidhu

    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

    Reply
  3. Ajith

    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

    Reply
  4. Prachi

    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.

    Reply
  5. Sidhu

    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

    Reply
  6. sami

    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.

    Reply
  7. Kostas Hairopoulos

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

    Nice tip and thank you for sharing with us

    Best Regards,
    khair

    Reply
  8. Kostas Hairopoulos

    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

    Reply
  9. Ayush

    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 ?

    Reply
  10. Sidhu Post author

    Ayush,

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

    Could you please come with some more details.

    Sidhu

    Reply

Leave a Reply to Sidhu Cancel reply

Your email address will not be published. Required fields are marked *