Re: Spool Oracle Tables into Excel Format
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
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
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
And do not forget to use ... WHERE ROWNUM<65537 :-))) 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
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
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 ||','||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
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 ||','||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
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).
RE: Spool Oracle Tables into Excel Format
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
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).
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).