|
Home > Archive > Oracle Server Tools > March 2005 > Best way to Extract Data from Oracle into Excel
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Best way to Extract Data from Oracle into Excel
|
|
|
| Hello,
I'm looking at ways to generate reports based on data in an Oracle
database. This data will be viewed in Excel.
I've looked at Oracle Reports and Oracle Discoverer as possible tools,
but reports created in either of the 2 applications lose their
formatting upon exporting to Excel.
Another method is to use VBA and ADO to connect to Oracle and extract
the data directly into Excel where the reports can then be formatted.
Does anyone have any ideas/comments regarding the above mentioned
techniques? What other approaches are there?
Thanks in advance,
H
| |
| Rauf Sarwar 2005-02-16, 7:50 am |
|
Hank wrote:
> Hello,
>
> I'm looking at ways to generate reports based on data in an Oracle
> database. This data will be viewed in Excel.
>
> I've looked at Oracle Reports and Oracle Discoverer as possible
tools,
> but reports created in either of the 2 applications lose their
> formatting upon exporting to Excel.
>
> Another method is to use VBA and ADO to connect to Oracle and extract
> the data directly into Excel where the reports can then be formatted.
>
> Does anyone have any ideas/comments regarding the above mentioned
> techniques? What other approaches are there?
>
> Thanks in advance,
> H
A lot can be said about MS but their office tools are excellent
neighbors among themselves i.e. Access, Excel, Word etc. So going from
Access/Excel to Oracle over ADO/OLEDB will be a good option. Then you
have a choice to present data in any of the tools without loosing any
formating.
Crystal reports also has a pretty good engine and works seamlessly with
Oracle.
Regards
/Rauf
| |
|
|
| Matthias Huebner 2005-02-16, 5:51 pm |
| Salut,
Is there realy a BEST WAY?
"Hank" <hank755_ca@yahoo.ca> schrieb im Newsbeitrag
news:58619410.0502152142.48630166@posting.google.com...
> Hello,
>
> I'm looking at ways to generate reports based on data in an Oracle
> database. This data will be viewed in Excel.
Try alternative OpenOffice you may use in Unix/Linux/Solaris Environment
too!
>
> I've looked at Oracle Reports and Oracle Discoverer as possible tools,
> but reports created in either of the 2 applications lose their
> formatting upon exporting to Excel.
with OpenOffice you may export directly to xls-books (Save As) without the
loss of any formatting.
also all edited formula will be translated to excel formulas.
>
> Another method is to use VBA and ADO to connect to Oracle and extract
> the data directly into Excel where the reports can then be formatted.
OpenOffice has a nice DataExplorer which is much more comfortable.
>
> Does anyone have any ideas/comments regarding the above mentioned
> techniques? What other approaches are there?
>
have a look at www.openoffice.org
> Thanks in advance,
> H
Cordialement
Matthias
| |
| IANAL_VISTA 2005-02-16, 5:51 pm |
| hank755_ca@yahoo.ca (Hank) wrote in
news:58619410.0502152142.48630166@posting.google.com:
> Hello,
>
> I'm looking at ways to generate reports based on data in an Oracle
> database. This data will be viewed in Excel.
>
> I've looked at Oracle Reports and Oracle Discoverer as possible tools,
> but reports created in either of the 2 applications lose their
> formatting upon exporting to Excel.
>
> Another method is to use VBA and ADO to connect to Oracle and extract
> the data directly into Excel where the reports can then be formatted.
>
> Does anyone have any ideas/comments regarding the above mentioned
> techniques? What other approaches are there?
>
> Thanks in advance,
> H
PERL can interoperate with Oracle (& many other DBs) & Excel.
| |
| George Lewycky 2005-02-16, 5:51 pm |
| Oracle has a program called Discover which is an ad-hoc/customizable
report tool which also has a EXCEL feature to xfer the output into a
excel spreadsheet
TOAD also has this extract feature
Good Luck
George
| |
| joel-garry@home.com 2005-02-16, 8:47 pm |
| Never used it, but always thought it looked interesting:
http://www.oraxcel.com/projects/sqlxl/
jg
--
@home.com is bogus.
Just went through big stupid project to make online thingee agree with
stupid excel.
| |
| Ingo Peters 2005-02-17, 2:47 am |
| On 2005-02-16, Hank <hank755_ca@yahoo.ca> wrote:
> I'm looking at ways to generate reports based on data in an Oracle
> database. This data will be viewed in Excel.
>
> I've looked at Oracle Reports and Oracle Discoverer as possible tools,
> but reports created in either of the 2 applications lose their
> formatting upon exporting to Excel.
Oracle Reports can produce formatted Excel reports via the web.
Actually, it produces HTML code that gets read and interpreted by Excel.
Regards,
Ingo
| |
|
| I have written a procedure in PL/SQL for Oracle Forms which takes
a block and export its columns into an external Excel file. In fact,
this file is an HTML with special tags recognized by the MS Office
tools. With these tags you can virutally create a sheet with any
functionality, event charts. An easy way to find which tag to use is
to create you customized Excel file and save it is as "Web Page".
Then you can edit the saved file in any text editor to view the
generated tags. The below procedure can be adapted to run on the
server too.
procedure cp_mwt_export_to_excel is
type t_format_mask is table of varchar2(30) index by binary_integer;
type t_item_type is table of varchar2(15) index by binary_integer;
type t_data_type is table of varchar2(15) index by binary_integer;
type t_multi_line is table of varchar2(5) index by binary_integer;
type t_display is table of boolean index by binary_integer;
type t_item_id is table of item index by binary_integer;
v_format_mask t_format_mask;
v_item_type t_item_type;
v_data_type t_data_type;
v_multi_line t_multi_line;
v_display t_display;
v_item_id t_item_id;
v_char varchar2(4000);
v_number number;
v_date date;
v_list_value varchar2(100);
v_prompt_text varchar2(60);
v_hint_text varchar2(60);
v_label varchar2(60);
v_user_url varchar2(50);
v_user_directory varchar2(50);
v_name varchar2(30);
v_filename varchar2(16);
v_status varchar2(15);
v_visible varchar2(5);
v_export_item varchar2(1);
v_rows pls_integer;
v_columns pls_integer;
v_lines pls_integer;
v_index pls_integer;
v_list_count pls_integer;
v_list_index pls_integer;
v_length pls_integer;
v_type pls_integer;
v_null boolean;
v_block_id block;
v_connection exec_sql.conntype;
v_cursor exec_sql.curstype;
v_handle text_io.file_type;
begin
enter;
if form_failure = true then
raise form_trigger_failure;
end if;
v_status := get_block_property('DATA',status);
if v_status = 'NEW' then
cp_mwt_display_error('There is no data to be exported.');
raise form_trigger_failure;
end if;
if v_status <> 'QUERY' then
cp_mwt_display_error('Save changes before exporting.');
raise form_trigger_failure;
end if;
cp_mwt_question('Export','Are you sure you want to export to Excel
(maximum of 65,535 records) ?');
v_filename := cf_mwt_generate_filename;
tool_env. getvar('USER_DIRECTORY',v_user_directory
);
v_handle :=
text_io. fopen(lower(v_user_directory||'\'||v_fil
ename)||'.xls','W');
text_io.put_line(v_handle,'<html
xmlns:o="urn:schemas-microsoft-com:office:office"');
text_io.put_line(v_handle,'xmlns:x="urn:schemas-microsoft-com:office:excel"');
text_io.put_line(v_handle,'xmlns="http://www.w3.org/TR/REC-html40">');
text_io.put_line(v_handle,'<body>');
text_io.put_line(v_handle,'<table border=1>');
text_io.put_line(v_handle,'<tr>');
v_connection := exec_sql.default_connection;
v_cursor := exec_sql.open_cursor(v_connection);
exec_sql. parse(v_connection,v_cursor,replace(get_
block_property('DATA',last_query),'ROWID
,'));
v_block_id := find_block('EXPORT');
v_columns := 0;
loop
v_columns := v_columns + 1;
begin
exec_sql. describe_column(v_connection,v_cursor,v_
columns,v_name,v_length,v_type);
exception
when exec_sql.invalid_column_number then
v_columns := v_columns - 1;
exit;
end;
v_item_id(v_columns) := find_item('DATA.'||v_name||'_DISPLAY');
if id_null(v_item_id(v_columns)) = true then
v_item_id(v_columns) := find_item('DATA.'||v_name||'_EDIT');
end if;
if id_null(v_item_id(v_columns)) = true then
v_item_id(v_columns) := find_item('DATA.'||v_name);
end if;
if id_null(v_block_id) = false then
v_export_item := name_in('EXPORT.'||v_name);
else
v_export_item := 'Y';
end if;
v_item_type(v_columns) :=
get_item_property(v_item_id(v_columns),i
tem_type);
v_data_type(v_columns) :=
get_item_property(v_item_id(v_columns),d
atatype);
v_visible := get_item_property(v_item_id(v_columns),v
isible);
v_prompt_text :=
get_item_property(v_item_id(v_columns),p
rompt_text);
v_hint_text :=
get_item_property(v_item_id(v_columns),h
int_text);
if v_item_type(v_columns) = 'TEXT ITEM' then
v_format_mask(v_columns) :=
get_item_property(v_item_id(v_columns),f
ormat_mask);
else
v_format_mask(v_columns) := null;
end if;
if v_hint_text is not null then
v_label := v_hint_text;
else
v_label := v_prompt_text;
end if;
if v_item_type(v_columns) <> 'DISPLAY ITEM' and v_visible =
'TRUE' and v_export_item = 'Y' then
v_display(v_columns) := true;
else
v_display(v_columns) := false;
end if;
if v_display(v_columns) = true then
text_io.put_line(v_handle,'<td bgcolor="#FFFF00"
x:autofilter="all"><b>'||v_label||'</b></td>');
end if;
if v_data_type(v_columns) = 'CHAR' then
if v_item_type(v_columns) = 'TEXT ITEM' then
v_multi_line(v_columns) :=
get_item_property(v_item_id(v_columns),m
ulti_line);
else
v_multi_line(v_columns) := 'FALSE';
end if;
exec_sql. define_column(v_connection,v_cursor,v_co
lumns,v_char,v_length);
end if;
if v_data_type(v_columns) = 'NUMBER' then
if v_format_mask(v_columns) is not null then
v_format_mask(v_columns) :=
replace(v_format_mask(v_columns),'9','#'
);
v_format_mask(v_columns) :=
replace(v_format_mask(v_columns),'G',','
);
v_format_mask(v_columns) :=
replace(v_format_mask(v_columns),'D','.');
end if;
v_multi_line(v_columns) := 'FALSE';
exec_sql. define_column(v_connection,v_cursor,v_co
lumns,v_number);
end if;
if v_data_type(v_columns) in ('DATE','DATETIME') then
v_multi_line(v_columns) := 'FALSE';
exec_sql. define_column(v_connection,v_cursor,v_co
lumns,v_date);
end if;
end loop;
text_io.put_line(v_handle,'</tr>');
v_rows := exec_sql.execute(v_connection,v_cursor);
v_lines := 0;
while exec_sql.fetch_rows(v_connection,v_cursor) > 0 loop
v_lines := v_lines + 1;
text_io.put_line(v_handle,'<tr>');
for v_index in 1..v_columns loop
v_null := false;
if v_data_type(v_index) = 'CHAR' then
exec_sql. column_value(v_connection,v_cursor,v_ind
ex,v_char);
if v_char is null then
v_null := true;
end if;
end if;
if v_data_type(v_index) = 'NUMBER' then
exec_sql. column_value(v_connection,v_cursor,v_ind
ex,v_number);
if v_number is null then
v_null := true;
end if;
end if;
if v_data_type(v_index) in ('DATE','DATETIME') then
exec_sql. column_value(v_connection,v_cursor,v_ind
ex,v_date);
if v_date is null then
v_null := true;
end if;
end if;
if v_display(v_index) = true then
if v_null = true then
text_io.put_line(v_handle,'<td></td>');
else
if v_data_type(v_index) = 'CHAR' then
v_char := replace(v_char,' ',' ');
if v_item_type(v_index) = 'CHECKBOX' then
if v_char in ('Y','YES') then
v_char := 'YES';
else
v_char := 'NO';
end if;
end if;
if v_item_type(v_index) = 'LIST' then
v_list_count :=
get_list_element_count(v_item_id(v_index
));
for v_list_index in 1..v_list_count loop
v_list_value :=
get_list_element_value(v_item_id(v_index
),v_list_index);
if v_list_value = v_char then
v_char :=
get_list_element_label(v_item_id(v_index
),v_list_index);
exit;
end if;
end loop;
end if;
if v_multi_line(v_index) = 'TRUE' then
text_io.put_line(v_handle,'<td
style=''white-space:wrap'' x:str>'||v_char||'</td>');
else
text_io.put_line(v_handle,'<td
style=''white-space:nowrap'' x:str>'||v_char||'</td>');
end if;
end if;
if v_data_type(v_index) = 'NUMBER' then
if v_format_mask(v_index) is not null then
text_io.put_line(v_handle,'<td
style=''mso-number-format:"'||v_format_mask(v_index)||'"''
x:num>'||to_char(v_number)||'</td>');
else
text_io.put_line(v_handle,'<td
x:num>'||to_char(v_number)||'</td>');
end if;
end if;
if v_data_type(v_index) = 'DATE' then
text_io.put_line(v_handle,'<td align=left
style=''mso-number-format:"dd-mmm-yyyy"''
x:num>'||to_char(v_date,'DD-MON-YYYY')||'</td>');
end if;
if v_data_type(v_index) = 'DATETIME' then
text_io.put_line(v_handle,'<td align=left
style=''mso-number-format:"dd-mmm-yyyy hh:mm:ss"''
x:num>'||to_char(v_date,'DD-MON-YYYY HH24:MI:SS')||'</td>');
end if;
end if;
end if;
end loop;
text_io.put_line(v_handle,'</tr>');
exit when v_lines = 65535;
end loop;
exec_sql.close_cursor(v_cursor);
exec_sql.close_connection(v_connection);
text_io.put_line(v_handle,'</table>');
text_io.put_line(v_handle,'</body>');
text_io.put_line(v_handle,'</html>');
text_io.fclose(v_handle);
if v_lines > 500 then
host('pkzip -add -silent
'||lower(v_user_directory||'\'||v_filena
me)||'.zip
'||lower(v_user_directory||'\'||v_filena
me)||'.xls');
host('del '||lower(v_user_directory||'\'||v_filena
me)||'.xls');
end if;
cp_mwt_display_message(to_char(v_lines)|
|' records were successfully
exported - the file will now be downloaded.');
tool_env.getvar('USER_URL',v_user_url);
if v_lines > 500 then
web. show_document(v_user_url||lower(v_filena
me)||'.zip','_blank');
else
web. show_document(v_user_url||lower(v_filena
me)||'.xls','_blank');
end if;
end;
| |
|
| I use Lotus Approach. Also, almost every reporting tool can do this.
"Hank" <hank755_ca@yahoo.ca> wrote in message
news:58619410.0502152142.48630166@posting.google.com...
> Hello,
>
> I'm looking at ways to generate reports based on data in an Oracle
> database. This data will be viewed in Excel.
>
> I've looked at Oracle Reports and Oracle Discoverer as possible tools,
> but reports created in either of the 2 applications lose their
> formatting upon exporting to Excel.
>
> Another method is to use VBA and ADO to connect to Oracle and extract
> the data directly into Excel where the reports can then be formatted.
>
> Does anyone have any ideas/comments regarding the above mentioned
> techniques? What other approaches are there?
>
> Thanks in advance,
> H
| |
| DA Morgan 2005-02-17, 5:52 pm |
| Hank wrote:
> Hello,
>
> I'm looking at ways to generate reports based on data in an Oracle
> database. This data will be viewed in Excel.
>
> I've looked at Oracle Reports and Oracle Discoverer as possible tools,
> but reports created in either of the 2 applications lose their
> formatting upon exporting to Excel.
>
> Another method is to use VBA and ADO to connect to Oracle and extract
> the data directly into Excel where the reports can then be formatted.
>
> Does anyone have any ideas/comments regarding the above mentioned
> techniques? What other approaches are there?
>
> Thanks in advance,
> H
Just a quick caution ... if your organization is subject to
Sarbanes-Oxley doing so could be illegal. Be very very careful.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
| |
| Waldhausen 2005-02-17, 5:52 pm |
| "Matthias Huebner" <info@verkehrsberatung.net> wrote in message news:<42136720$0$17767$626a14ce@news.free.fr>...
> Is there realy a BEST WAY?
>
Colleague,
.... at all? Almost all of -est belongs to ideal (not substantial)
category of abstracts.
[vbcol=seagreen]
> "Hank" <hank755_ca@yahoo.ca> schrieb im Newsbeitrag
> news:58619410.0502152142.48630166@posting.google.com...
[... sorry ...]
--
test,
WS1-UANIC
| |
| Waldhausen 2005-02-17, 5:52 pm |
| DA Morgan <damorgan@x.washington.edu> wrote in message news:<1108658143.99405@yasure>...
> Hank wrote:
>
"Generate"... Do you mean "run" instead?
In other words do you plan to provide a possibility to construct the
SQL to those who will choose your way? Or they should just give the
parameters for your template statements?
"Excel"... What about other spreadsheet (i.e. KSpread)?
[vbcol=seagreen]
<off-topic>
Discoverer was a powerful tool when was used. Is Excel is more now?
What you can do by excel and can't by discoverer?
</>
What sort of "formatting" you care of?
[vbcol=seagreen]
(oj)dbc
[vbcol=seagreen]
>
Portlets may be customized for Excel-like output.
> Just a quick caution ... if your organization is subject to
> Sarbanes-Oxley doing so could be illegal. Be very very careful.
Doing what, dear DA? Explain please... And... mmm... If YOUSOKIND,
cast your conception of "Sarbanes-Oxley" impact to us.
--
wbr,
not a thought-reeder [yet],
WS1-UANIC
P.S. To ignore you may want to replace the YOUSOKIND above with 'two
questions from a guy without the "majority" are not so much for you'
or anything u like better.
| |
| prasad yammanur 2005-02-17, 8:48 pm |
| Hank,
I have used this
(http://www.oracle.com/technology/pr...ddin/index.html)
Spreadsheet add-in in my
previous job. It worked good. Installation takes less than a minute and you
can connect to Oracle OLAP data. Try this and see if it serves your purpose.
regards
Prasad Yammanur
"Hank" <hank755_ca@yahoo.ca> wrote in message
news:58619410.0502152142.48630166@posting.google.com...
> Hello,
>
> I'm looking at ways to generate reports based on data in an Oracle
> database. This data will be viewed in Excel.
>
> I've looked at Oracle Reports and Oracle Discoverer as possible tools,
> but reports created in either of the 2 applications lose their
> formatting upon exporting to Excel.
>
> Another method is to use VBA and ADO to connect to Oracle and extract
> the data directly into Excel where the reports can then be formatted.
>
> Does anyone have any ideas/comments regarding the above mentioned
> techniques? What other approaches are there?
>
> Thanks in advance,
> H
| |
| Ingo Peters 2005-02-17, 8:48 pm |
| On 2005-02-17, MBPP <mpacheco@directnet.com.br> wrote:
> I have written a procedure in PL/SQL for Oracle Forms which takes
> a block and export its columns into an external Excel file. In fact,
Very nice idea! I think I'll have to try that out sometime. Deployed as
a web form it could popup a new browser window with the right mimetype,
and voila, instant data export. I wonder if it'd be possible to make it
more generic, so that it would work with any block you point it to?
Hmmm...., I'll definitely give this a whirl.
Ingo
| |
|
| The routine I pasted is generic because it takes the last query
executed on the block where the cursor is located. If you use IE,
the file will be automatically opened by Excel inside the browser.
It can be adapted to run on the DB server too and if you have
Apache/MOD_PLSQL you don't even need to write to an external
file, just set the MIME header and output everything to the buffer
via HTP.PRINT.
| |
| DA Morgan 2005-02-20, 6:14 pm |
| Waldhausen wrote:
>
> Doing what, dear DA? Explain please... And... mmm... If YOUSOKIND,
> cast your conception of "Sarbanes-Oxley" impact to us.
Lets work backwards on this one.
Numbers in a spreadsheet are used to make a financial decision. Can
you audit the source of those numbers? How they got there? And trace
them back to aggregates from specific financial transactions?
A copy of a spreadsheet is given to a financial analyst from a news
organization and reported to stockholders. Can you audit the source
of those numbers? How they got there? And trace them back to specific
financial transactions or inventory levels or valuations?
Can you guarantee the laptop containing them won't be stolen?
Can you guarantee the hard disk containing them is backed up?
Can you guarantee the versioning of every "SAVE" and audit the changes?
Would you be willing to go to jail if you were the CIO and some geek
in IT said "don't worry".
An increasing number of CFO's are saying "Hell no" and they are right.
I was at a meeting just earlier this week with a CFO whose organization
is not covered by Sarbanes-Oxley (they are a non-profit) and he was well
aware of the legal liabilities were that to apply: And so was his
attorney.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
| |
|
| People recommended different tools here. I know that Fastreader will mostlikely will work for you. In particular if you have large tables in production Oracle environment.
Issues in production environment:
you need a tool that will meet two main things: high speed and low system overhead, must be during unload data from production database.
FastReader www.wisdomforce.com will export data from Oracle tables into flat csv file with preconfigured delimiter.
Than this flat file you can open with excel. We made a benchmark when evaluated FastReader. It took few minutes to unload 7O+ million rows from Oracle table into csv file. The was no overhead on production database, since FastReader soesn't use Oracle process during export. And we used only 1 CPU. When we utilized parallel unload with Fastreader, gains in performance was tramendeous!
quote:
Originally posted by Hank
Hello,
I'm looking at ways to generate reports based on data in an Oracle
database. This data will be viewed in Excel.
I've looked at Oracle Reports and Oracle Discoverer as possible tools,
but reports created in either of the 2 applications lose their
formatting upon exporting to Excel.
Another method is to use VBA and ADO to connect to Oracle and extract
the data directly into Excel where the reports can then be formatted.
Does anyone have any ideas/comments regarding the above mentioned
techniques? What other approaches are there?
Thanks in advance,
H
|
|
|
|
|