RE: Oracle to Excel

Laura,  

Check out Tom Kyte's site. He has posted the source code for a PL/SQL package called OWA_SYLK that will dump a SQL query to a *.SLK file (*.SLK files can be directly opened by Excel). Plug in the SQL and then go... send the output file to the user and they do the rest.  

Here is the link:  

http://asktom.oracle.com/pls/ask/f?p=4950:8:1556583::NO::F4950_P8_DISPLAYID, F4950_P8_CRITERIA:828426949078,%7Bowa_sylk%7D <http://asktom.oracle.com/pls/ask/f?p=4950:8:1556583::NO::F4950_P8_DISPLAYID ,F4950_P8_CRITERIA:828426949078,%7Bowa_sylk%7D>  

We use it constantly!  

Greg

-----Original Message-----

Sent: Wednesday, November 06, 2002 4:00 PM To: Multiple recipients of list ORACLE-L

Thank you for your response Tom. I received other responses as well, and I know that I can comma delimit a .txt file but I wanted to know if I could do it all at one time, which is why I tried your suggestion first. It works except that each field is in a separate row instead of a column.  

Below is an example (1 record is shown):  

53-041-02

0

2

AL POWER CO 51271

27-Nov-01

173.52

447-57

D412

173.52

226 DONNELL 43000

00014.04.D412    

It looks like it thinks it is a carriage return instead of a tab. Any ideas?  

Laura  

-----Original Message-----

Sent: Wednesday, November 06, 2002 1:24 PM To: Multiple recipients of list ORACLE-L  

Laura,  

lots of tools do this for you automatically. Oracle discoverer will export the results of a query directly into Excel format.  

You can do this yourself as follows:  

in SqlPLus  

select col1||chr(10)||col2||chr(10)

from table.  

the chr(10) is the tab character.

spool the results to a output.xls  

open the file using excel. the tab character is the default column delimiter. You should see all your data in cells.  

hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

Sent: Wednesday, November 06, 2002 1:49 PM To: Multiple recipients of list ORACLE-L

I think I have seen traffic concerning the extracting of data from Oracle into an Excel spreadsheet. We now have a need for this. Could anyone enlighten me?

Thank you in advance.

Laura

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mirsky, Greg
  INET: gmirsky_at_Estee.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).