RE: sql o/p to Excel
Hi Ravindra, Bein in the same situation like you a few months ago I manage to figure out 2 solutions at your problem: 1. There is a nice Excel add-in called SecondWind which is able to perform export operations from Oracle to Excel in a very nice manner and formatted in the way which you described below. 2. You can open a data source using Oracle ODBC driver. From Excel, you can start Microsoft Query and you are able to place the results in a spreadsheet. Regards, Pierre -Original Message- From: ravindra [SMTP:[EMAIL PROTECTED]] Sent: Friday, June 22, 2001 4:10 AM To: ORACLE-L Cc: ravindra Subject:sql o/p to Excel I have a sql script to generate a dialy report like this. 01-JUN-200107 84 17 18 11 *** - MAXIMUM No. 11 TOTAL 15 04-JUN-200108 92 10 111 11 122 12 133 13 14 16 14 154 17 182 18 192 *** - MAXIMUM No. 16 TOTAL 32 I want to present this output in excel so that I can draw graphs.I am not able to open the file in excel in alligned format ie each column must appear in different columns in excel.How can I do that. Is that possible? Thanks Ravindra -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ravindra Basavaraja INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sql o/p to Excel
Hi
I heard, that there is a OLE2 package in Forms 6, which has capability to
create Excel file from blocks on forms.
Below is an example that one guy sent me about this issue... Hope it helps -
check the package in forms.
CREATE or REPLACE FORCE PROCEDURE export_transaction_to_excel IS
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheets ole2.obj_type;
worksheet ole2.obj_type;
cell ole2.obj_type;
rowcounter number:=1;
local_cursor_record number:=:system.cursor_record;
old_cursor_style varchar2(100);
errors_occured boolean:=false;
ole_error exception;
pragma exception_init(ole_error, -305500);
my_alert_id alert;
alert_respnse number;
procedure place_value_in_cell(rownum_in in number,colnum_in in
number,value_in in varchar2) is
args ole2.list_type;
begin
args:=ole2.create_arglist;
ole2.add_arg(args,rownum_in);
ole2.add_arg(args,colnum_in);
--cell:=ole2.invoke_obj(worksheet,'Cells',args);
cell:=ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
ole2.set_property(cell,'Value',value_in);
ole2.release_obj(cell);
end place_value_in_cell;
procedure savespreadsheet is
args ole2.list_type;
vDatestamp varchar2(20);
begin
vDatestamp:=to_char(sysdate,'mmddy')| |'-'|
|to_char(sysdate,'hh24miss');
args:=ole2.create_arglist;
ole2.add_arg(args,'C:\SSLO\TESTFORM.XLS');
ole2.invoke(worksheet,'SaveAs',args);
ole2.destroy_arglist(args);
ole2.invoke(application,'Quit');
end savespreadsheet;
procedure open_excel_workbook is
begin
application:=ole2.create_obj('Excel.Application');
workbooks:=ole2.get_obj_property(application,'Workbooks');
workbook:=ole2.invoke_obj(workbooks,'Add');
worksheets:=ole2.get_obj_property(application,'Worksheets');
worksheet:=ole2.invoke_obj(worksheets,'Add');
end open_excel_workbook;
procedure write_column_header is
begin
place_value_in_cell(rowcounter,1,'Depart No');
place_value_in_cell(rowcounter,2,'Depart Name');
place_value_in_cell(rowcounter,3,'Loc');
rowcounter:=rowcounter+1;
end write_column_header;
procedure export_the_data is
original_receipt number;
original_date date;
original_transmital_number number;
begin
go_block('dept');
first_record;
loop
place_value_in_cell(rowcounter,1,to_char(:deptno));
place_value_in_cell(rowcounter,2,:dname);
place_value_in_cell(rowcounter,3,:loc);
exit when :system.last_record='TRUE';
next_record;
rowcounter:=rowcounter+1;
end loop;
end export_the_data;
BEGIN
begin
old_cursor_style:=get_application_property(cursor_style);
set_application_property(cursor_style,'BUSY');
open_excel_workbook;
write_column_header;
export_the_data;
exception
when form_trigger_failure then
raise;
when ole_error then
message('error sending data to excel');
message(' ');
errors_occured:=true;
end;
savespreadsheet;
ole2.release_obj(worksheet);
ole2.release_obj(worksheets);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);
if not errors_occured then
go_record(local_cursor_record);
end if;
set_application_property(cursor_style,old_cursor_style);
END export_transaction_to_excel;
Rok
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
[EMAIL PROTECTED]
Sent: 22. junij 2001 10:46
To: Multiple recipients of list ORACLE-L
Subject: RE: sql o/p to Excel
Importance: Low
Hi Ravindra,
Bein in the same situation like you a few months ago I manage to
figure out 2
solutions at your problem:
1. There is a nice Excel add-in called SecondWind which is able
to perform
export operations from Oracle to Excel in a very nice manner and
formatted in
the way which you described below.
2. You can open a data source using Oracle ODBC driver. From
Excel, you can
start Microsoft Query and you are able to place the results in a
spreadsheet.
Regards,
Pierre
-Original Message-
From:ravindra [SMTP:[EMAIL PROTECTED]]
Sent:Friday, June 22, 2001 4:10 AM
To: ORACLE-L
Cc: ravindra
Subject: sql o/p to Excel
I have a sql script to generate a dialy report like this.
01-JUN-200107 84
17 18 11
*** -
MAXIMUM No. 11
TOTAL 15
04-JUN-200108 92
10 111
11 122
12 133
13
sql o/p to Excel
I have a sql script to generate a dialy report like this. 01-JUN-200107 84 17 18 11 *** - MAXIMUM No. 11 TOTAL 15 04-JUN-200108 92 10 111 11 122 12 133 13 14 16 14 154 17 182 18 192 *** - MAXIMUM No. 16 TOTAL 32 I want to present this output in excel so that I can draw graphs.I am not able to open the file in excel in alligned format ie each column must appear in different columns in excel.How can I do that. Is that possible? Thanks Ravindra -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ravindra Basavaraja INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: sql o/p to Excel
Ravindra, Yep its possible to transform your o/p from spool into excel, but you will need to format your data before extraction. you can do this by select fieldname||chr(32)||fieldname from blah; where chr(32) represents the space character, which will help you to print in a different column in excel. hope this helps. raja -- On Thu, 21 Jun 2001 18:10:20 Ravindra Basavaraja wrote: I have a sql script to generate a dialy report like this. 01-JUN-200107 84 17 18 11 *** - MAXIMUM No. 11 TOTAL 15 04-JUN-200108 92 10 111 11 122 12 133 13 14 16 14 154 17 182 18 192 *** - MAXIMUM No. 16 TOTAL 32 I want to present this output in excel so that I can draw graphs.I am not able to open the file in excel in alligned format ie each column must appear in different columns in excel.How can I do that. Is that possible? Thanks Ravindra -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ravindra Basavaraja INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: sql o/p to Excel
Dear Ravindra , You can use this output itself. Just select the fixed width option instead of the delimited option. Then put you own column lines where you want them to be. Regards Ravindra Basavaraja To: Multiple recipients of list ORACLE-L ravindra@sen[EMAIL PROTECTED] tica.comcc: Sent by: Subject: sql o/p to Excel root@fatcity. com 22-Jun-2001 10:10 AM Please respond to ORACLE-L Sender Info: No Sender Info found in the address Book I have a sql script to generate a dialy report like this. 01-JUN-200107 84 17 18 11 *** - MAXIMUM No. 11 TOTAL 15 04-JUN-200108 92 10 111 11 122 12 133 13 14 16 14 154 17 182 18 192 *** - MAXIMUM No. 16 TOTAL 32 I want to present this output in excel so that I can draw graphs.I am not able to open the file in excel in alligned format ie each column must appear in different columns in excel.How can I do that. Is that possible? Thanks Ravindra -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ravindra Basavaraja INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Visit us at www.singaporeair.com. __ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
