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 onAnd the xls it makes shows up like:
spool c:\salgrade.xls
select * from salgrade;
spool off
set markup html off spool off
Sidhu
Friday, June 15, 2007
Spool to a .xls (excel) file...
Sidhu
Subscribe to:
Post Comments (Atom)
28 comments:
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
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
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
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
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
Hi ,
I found a solution. It needs the lines to be set to some value . The value i was setting was less.
So simple :) I got into too technical things :D
Sidhu
Satsriakal Amardeep,
I am using 9i and the xls produced by your instructions does not divide the fields in excel. Each line is in the same excel field. It does not appear like your screenshot. How do I correct it in sqlplus?
Many Thanks,
Josh
SSA...
It shouldn't be the case. It might not be showing the borders for cells but definitely its going to write it cell wise. Can you check again ?
Amardeep
I missed the 'on' after 'markup html'. Works perfectly. Thanks
Great :)
I have an unrelated oracle question. I work with 9i, and any changes have to be done via the application. Update queries in sqlplus will cause the index to not match the data and prevent the application from reading the table correctly, or at all. Google isn't too helpful with this. I just want to run an update query, and update the index as well. Is this possible?
Thanks,
Josh
Joosh
I didn't really get your question. Do you mean to update indexes ? Thats done automatically by Oracle when you run an update, delete or insert statement.
I guess it may have to do with the application then. I've been told from the vendor that running any updates, deletes, or inserts will cause a mismatch with the index and cause the application to not work (I've discovered this to be true, the hard way). I found out by running an update on a table, and then no applications were able to update or insert to that table until I reverted my update query. The vendor has a command line exe to use to reindex the table that was updated. I'm a bit more familiar with MySQL and I've never had issues with table modifying queries, but yet they will cause problems with the Oracle db pertaining to indexing. So this may be application dependent, any thoughts?
I guess it has something to do with the application design. Can you share the application architecture details at broader level like how exactly it looks like ?
I no more access this blog. So please post it on http://amardeepsidhu.com/blog or mail me directly at amardeepsidhu at amardeepsidhu dot com.
Hi sidhu syed here,
I found ur spooling code suitable for my for reporting in my project. I have problems executing this code via JDBC in JAVA. Can u Plz help me out in executing this spooling code in oracle using JAVA?
Excelent.!!! Working Perfect... Thank´s a lot
Hey Sidhu thanks a lot this is working perfectly for me. I was searching this code for almost a month. Great sidhu
Excellent QUERY ... Worked out !)0% for me !!!
Thnx
Regards,
Ashish .A. Poojary
Blogging @ http://ashishp0911.blogspot.com
Sorry for the Splelling Error.
I mean 100 % Perfect !!!
Regards,
Ashish .A. Poojary
Blogging @ ashishp0911.blogspot.com
I want to insert the data which is there in xcel sheet to oracle table(one i have created already).
Is there any way other than using .ctl and .dat files???
Can i directly insert data from xcel sheet to oracle table by connecting to its db?
This is not working at all.
Raju,
What problem are you facing ?
Hi Amardeep,
I tried your spool command, its not working for me n the sql prompt. Here is what i wrote-
spool Desktop:\testdata.xls
> select first 5 ltxn_code from ltxns;
> spool off
Actually i have written a script which gives me some output and i want to export that output to an excel sheet. Can you help me in this.
ashish
Thanks for the script.But I am facing some issue with formating the xls colum that we got after running this spool command.The value is showing like 9.00E+015 instead of9000000000060010 and even after formating to number the value not showing correctly..Please advice.
Works as expected but I am having an issue with the excel side. When I view the output, it add the SPOOL syntax as a header and footer which I do not want as well as inserting a new column header row for each instance where the query finds a new set of data (so to speak).
In addition, when I attempt to open this as a simply table using ARCGIS products (such as ArcCatalog) it can not interpret the format of the spreadsheet.
This might be due to the HTML format
It helps me a lot
Thx Sidhu
Hi Sidhu,
I ran the script and I got message
line 1: SQLPLUS Command Skipped: set markup html off spool off
I am having oracle 11g and using SQL developer 1.5.5
Can you please help me with this?
Thanks. :)
Post a Comment