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
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
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 😀
Sidhu
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.
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.
Is any way to import from “dbms_output” into Excel file?
Nice tip and thank you for sharing with us
Best Regards,
khair
@Kostas
What exactly are you trying to print using dbms_output ?
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
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 ?
Ayush,
I am not sure if i exactly got what you are trying to achieve ?
Could you please come with some more details.
Sidhu
hi
the script which you shared on your blog is not fine its not working in linux