RE: Spool Oracle Tables into Excel Format

2002-09-23 Thread Mercadante, Thomas F 

Bob,

All of the options stated by people from this list are excellent.

One thing that was not mentioned was that Excel can read any type of text
file.  It will notice that it is a text file, and will provide you with the
opportunity to declare what the column delimiter is.

What this means is that you can use any character as a field delimiter, tell
Excel what that column is, and it will convert the file for you.

By default, Excel likes the TAB char as a column delimiter.  So, you can
write your query as such:

select column1||char(10)||column2||char(10)||column3||char(10) from
table_name.

Spool the output and name the file newfile.txt;  start Excel, and file|open
the file and follow the help wizard.  I do this all the time.

Hope this helps.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, September 20, 2002 4:33 PM
To: Multiple recipients of list ORACLE-L


All,

Is it possible to create Oracle reports into Excel
format ? 
Is it possible to spool Oracle tables into Excel
format?

Thanks in Advance
Bob

__
Do you Yahoo!?
New DSL Internet Access from SBC  Yahoo!
http://sbc.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bob Robert
  INET: [EMAIL PROTECTED]

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: [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: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

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: [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: Spool Oracle Tables into Excel Format

2002-09-23 Thread Jan Pruner 

And do not forget to use ... WHERE ROWNUM65537 :-)))

JP

On Monday 23 September 2002 15:13, you wrote:
 Bob,

 All of the options stated by people from this list are excellent.

 One thing that was not mentioned was that Excel can read any type of text
 file.  It will notice that it is a text file, and will provide you with the
 opportunity to declare what the column delimiter is.

 What this means is that you can use any character as a field delimiter,
 tell Excel what that column is, and it will convert the file for you.

 By default, Excel likes the TAB char as a column delimiter.  So, you can
 write your query as such:

 select column1||char(10)||column2||char(10)||column3||char(10) from
 table_name.

 Spool the output and name the file newfile.txt;  start Excel, and file|open
 the file and follow the help wizard.  I do this all the time.

 Hope this helps.

 Tom Mercadante
 Oracle Certified Professional


 -Original Message-
 Sent: Friday, September 20, 2002 4:33 PM
 To: Multiple recipients of list ORACLE-L


 All,

 Is it possible to create Oracle reports into Excel
 format ?
 Is it possible to spool Oracle tables into Excel
 format?

 Thanks in Advance
 Bob

 __
 Do you Yahoo!?
 New DSL Internet Access from SBC  Yahoo!
 http://sbc.yahoo.com

-- 
 Pruner Jan
   [EMAIL PROTECTED]
 http://jan.pruner.cz/
-
Only Robinson Crusoe had all his work done by Friday
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jan Pruner
  INET: [EMAIL PROTECTED]

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: [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: Spool Oracle Tables into Excel Format

2002-09-23 Thread Inka Bezdziecka 

I have never thought that I would ever suggest using  GUI tool over a command line. 
Perhaps there is a first time for everything.

So, may I add to the list of good advices: use T.O.A.D. from http://www.quest.com/toad/
Disclaimer: I am not in any form affiliated with Quest Software nor I try to enforce 
or market its products. I have just found that I am too lazy to use SQL*Plus all the 
time.

inka

-Original Message-
Sent: Monday, September 23, 2002 9:13 AM
To: Multiple recipients of list ORACLE-L


Bob,

All of the options stated by people from this list are excellent.

One thing that was not mentioned was that Excel can read any type of text
file.  It will notice that it is a text file, and will provide you with the
opportunity to declare what the column delimiter is.

What this means is that you can use any character as a field delimiter, tell
Excel what that column is, and it will convert the file for you.

By default, Excel likes the TAB char as a column delimiter.  So, you can
write your query as such:

select column1||char(10)||column2||char(10)||column3||char(10) from
table_name.

Spool the output and name the file newfile.txt;  start Excel, and file|open
the file and follow the help wizard.  I do this all the time.

Hope this helps.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, September 20, 2002 4:33 PM
To: Multiple recipients of list ORACLE-L


All,

Is it possible to create Oracle reports into Excel
format ? 
Is it possible to spool Oracle tables into Excel
format?

Thanks in Advance
Bob

__
Do you Yahoo!?
New DSL Internet Access from SBC  Yahoo!
http://sbc.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bob Robert
  INET: [EMAIL PROTECTED]

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: [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: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

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: [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: Inka Bezdziecka
  INET: [EMAIL PROTECTED]

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: [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: Spool Oracle Tables into Excel Format

2002-09-23 Thread Jan Pruner 

For small number of rows only.

JP
BTW: Free HTML report tool http://www.allroundautomations.com/bodyqr.html

On Monday 23 September 2002 16:23, you wrote:
 I have never thought that I would ever suggest using  GUI tool over a
 command line. Perhaps there is a first time for everything.

 So, may I add to the list of good advices: use T.O.A.D. from
 http://www.quest.com/toad/ Disclaimer: I am not in any form affiliated with
 Quest Software nor I try to enforce or market its products. I have just
 found that I am too lazy to use SQL*Plus all the time.

 inka

 -Original Message-
 Sent: Monday, September 23, 2002 9:13 AM
 To: Multiple recipients of list ORACLE-L


 Bob,

 All of the options stated by people from this list are excellent.

 One thing that was not mentioned was that Excel can read any type of text
 file.  It will notice that it is a text file, and will provide you with the
 opportunity to declare what the column delimiter is.

 What this means is that you can use any character as a field delimiter,
 tell Excel what that column is, and it will convert the file for you.

 By default, Excel likes the TAB char as a column delimiter.  So, you can
 write your query as such:

 select column1||char(10)||column2||char(10)||column3||char(10) from
 table_name.

 Spool the output and name the file newfile.txt;  start Excel, and file|open
 the file and follow the help wizard.  I do this all the time.

 Hope this helps.

 Tom Mercadante
 Oracle Certified Professional


 -Original Message-
 Sent: Friday, September 20, 2002 4:33 PM
 To: Multiple recipients of list ORACLE-L


 All,

 Is it possible to create Oracle reports into Excel
 format ?
 Is it possible to spool Oracle tables into Excel
 format?

 Thanks in Advance
 Bob

 __
 Do you Yahoo!?
 New DSL Internet Access from SBC  Yahoo!
 http://sbc.yahoo.com

-- 
 Pruner Jan
   [EMAIL PROTECTED]
 http://jan.pruner.cz/
-
Only Robinson Crusoe had all his work done by Friday
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jan Pruner
  INET: [EMAIL PROTECTED]

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: [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: Spool Oracle Tables into Excel Format

2002-09-23 Thread Jared Still 


This seems to be coming up a lot lately, and I've had to do it myself 
recently.

This is fairly easy to do with Perl.  I think I posted something about
this within the past month, so you may want to dig in the archives
for some details.

Jared

On Friday 20 September 2002 14:03, Aponte, Tony wrote:
 There were some posts recently suggesting the spooling of the columns using
 a comma as a separator.  But check out this article in XML Journal
 (http://www.syntelinc.com/syntel/english/0072/SYNT_XMLjrnl.pdf).  It
 shows how to build preformatted Excel reports with XML, but it requires
 Excel 2002.

 HTH
 Tony Aponte

 -Original Message-
 Sent: Friday, September 20, 2002 4:33 PM
 To: Multiple recipients of list ORACLE-L


 All,

 Is it possible to create Oracle reports into Excel
 format ?
 Is it possible to spool Oracle tables into Excel
 format?

 Thanks in Advance
 Bob

 __
 Do you Yahoo!?
 New DSL Internet Access from SBC  Yahoo!
 http://sbc.yahoo.com


Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1
Content-Transfer-Encoding: quoted-printable
Content-Description: 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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: [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: Spool Oracle Tables into Excel Format

2002-09-21 Thread Naveen Nahata 



You 
don't need to get rid of the commas. A CSV file will automatically be read into 
excel and all the commas will become a breakpoint for new columns. Or 
alternatively a spooled output can be read into excel, it will open the format 
wizard to specify the breakpoints for columns

But 
the question is what if the comma is within the value of a column? In that case 
excel will create one more column. How to overcome that in a spooled CSV 
file?

Naveen

  -Original Message-From: Viktor 
  [mailto:[EMAIL PROTECTED]]Sent: Saturday, September 21, 2002 2:40 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  Re:Spool Oracle Tables into Excel Format
  By the way, would you use replace to get rid of the commas withiin fields 
  so that Excel reads it properly? 
  [EMAIL PROTECTED] wrote: 
  Bob,You 
can create a CSV file from SQL*Plus with the following 
statement:select 
Date: 9/20/2002 12:33 PMAll,Is it 
possible to create Oracle reports into Excelformat ? Is it possible 
to spool Oracle tables into Excelformat?Thanks in 
AdvanceBob__Do 
you Yahoo!?New DSL Internet Access from SBC  
Yahoo!http://sbc.yahoo.com-- Please see the official ORACLE-L 
FAQ: http://www.orafaq.com-- Author: Bob RobertINET: 
[EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 
http://www.fatcity.comSan Diego! ! , California -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe 
message BODY, include a line containing: UNSUB ORACLE-L(or the name of 
mailing list you want to be removed from). You mayalso 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 
http://www.fatcity.comSan Diego, California -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe 
message BODY, include a line containing: UNSUB! ! ORACLE-L(or the name 
of mailing list you want to be removed from). You mayalso send the HELP 
command for other information (like subscribing).
  
  
  Do you Yahoo!?New DSL Internet 
  Access from SBC  Yahoo!

Re: Spool Oracle Tables into Excel Format

2002-09-21 Thread Tim Gorman 



Delimiting each column with surrounding 
double-quotes as well as commas is pretty standard...

Another standard approach is to delimit with a 
character that is less likely to be part of the data, such as the "tilde" or "~" 
character...

  - Original Message - 
  From: 
  Naveen Nahata 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Saturday, September 21, 2002 7:08 
  AM
  Subject: RE: Spool Oracle Tables into 
  Excel Format
  
  You 
  don't need to get rid of the commas. A CSV file will automatically be read 
  into excel and all the commas will become a breakpoint for new columns. Or 
  alternatively a spooled output can be read into excel, it will open the format 
  wizard to specify the breakpoints for columns
  
  But 
  the question is what if the comma is within the value of a column? In that 
  case excel will create one more column. How to overcome that in a spooled CSV 
  file?
  
  Naveen
  
-Original Message-From: Viktor 
[mailto:[EMAIL PROTECTED]]Sent: Saturday, September 21, 2002 
2:40 AMTo: Multiple recipients of list 
ORACLE-LSubject: Re:Spool Oracle Tables into Excel 
Format
By the way, would you use replace to get rid of the commas withiin fields 
so that Excel reads it properly? 
[EMAIL PROTECTED] wrote: 
Bob,You 
  can create a CSV file from SQL*Plus with the following 
  statement:select 
  <COLUMN_NAME||','','||from 
  where ..;Dick 
  GouletReply 
  SeparatorAuthor: Bob Robert 
  <[EMAIL PROTECTED]>Date: 9/20/2002 12:33 PMAll,Is 
  it possible to create Oracle reports into Excelformat ? Is it 
  possible to spool Oracle tables into Excelformat?Thanks in 
  AdvanceBob__Do 
  you Yahoo!?New DSL Internet Access from SBC  
  Yahoo!http://sbc.yahoo.com-- Please see the official ORACLE-L 
  FAQ: http://www.orafaq.com-- Author: Bob RobertINET: 
  [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 
  http://www.fatcity.comSan Diego! ! , California -- Mailing list and 
  web hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe 
  message BODY, include a line containing: UNSUB ORACLE-L(or the name of 
  mailing list you want to be removed from). You mayalso 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 
  http://www.fatcity.comSan Diego, California -- Mailing list and web 
  hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe 
  message BODY, include a line containing: UNSUB! ! ORACLE-L(or the name 
  of mailing list you want to be removed from). You mayalso send the 
  HELP command for other information (like subscribing).


Do you Yahoo!?New DSL Internet 
Access from SBC  Yahoo!

RE: Spool Oracle Tables into Excel Format

2002-09-20 Thread Feng, Jun 

Sure. Use ',' as colsep, and have file extension csv.

Jun
-Original Message-
Sent: Friday, September 20, 2002 4:33 PM
To: Multiple recipients of list ORACLE-L


All,

Is it possible to create Oracle reports into Excel
format ? 
Is it possible to spool Oracle tables into Excel
format?

Thanks in Advance
Bob

__
Do you Yahoo!?
New DSL Internet Access from SBC  Yahoo!
http://sbc.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bob Robert
  INET: [EMAIL PROTECTED]

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: [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: Feng, Jun
  INET: [EMAIL PROTECTED]

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: [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: Spool Oracle Tables into Excel Format

2002-09-20 Thread Aponte, Tony 
Title: RE: Spool Oracle Tables into Excel Format






There were some posts recently suggesting the spooling of the columns using a comma as a separator. But check out this article in XML Journal (http://www.syntelinc.com/syntel/english/0072/SYNT_XMLjrnl.pdf). It shows how to build preformatted Excel reports with XML, but it requires Excel 2002.

HTH

Tony Aponte


-Original Message-

From: Bob Robert [mailto:[EMAIL PROTECTED]]

Sent: Friday, September 20, 2002 4:33 PM

To: Multiple recipients of list ORACLE-L

Subject: Spool Oracle Tables into Excel Format



All,


Is it possible to create Oracle reports into Excel

format ? 

Is it possible to spool Oracle tables into Excel

format?


Thanks in Advance

Bob


__

Do you Yahoo!?

New DSL Internet Access from SBC  Yahoo!

http://sbc.yahoo.com

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Bob Robert

 INET: [EMAIL PROTECTED]


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: [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: Spool Oracle Tables into Excel Format

2002-09-20 Thread Reginald . W . Bailey 


You can spool Oracle output into text files using SQL*Plus. Excel can
handle plain text and Comma Delimitted Text files.

RWB




Bob Robert [EMAIL PROTECTED]@fatcity.com on 09/20/2002 03:33:24 PM

Please respond to [EMAIL PROTECTED]



Sent by:  [EMAIL PROTECTED]


To:   Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:


All,

Is it possible to create Oracle reports into Excel
format ?
Is it possible to spool Oracle tables into Excel
format?

Thanks in Advance
Bob

__
Do you Yahoo!?
New DSL Internet Access from SBC  Yahoo!
http://sbc.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bob Robert
  INET: [EMAIL PROTECTED]

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: [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 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: [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).