Home » SQL & PL/SQL » SQL & PL/SQL » How to transfer data from Oracle to Excel (Oracle 10.2.0.1)
How to transfer data from Oracle to Excel [message #320418] Thu, 15 May 2008 01:54 Go to next message
navaneethdba
Messages: 71
Registered: July 2007
Location: chennai
Member
Hi,

Is it possible to convert Oracle table data in to an excel file using Oracle ?

Version:10.2.0.1

please Advice...

TIA,
Re: How to transfer data from Oracle to Excel [message #320420 is a reply to message #320418] Thu, 15 May 2008 02:02 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
An easy way to do that is to create a CSV file (as select from one or more Oracle tables) and open this file in Excel.
Re: How to transfer data from Oracle to Excel [message #320427 is a reply to message #320418] Thu, 15 May 2008 02:12 Go to previous messageGo to next message
jiltin
Messages: 44
Registered: September 2002
Member
If you need to download using sql, you can append chr(9) with every column you select, and get the output as a csv file.

Excel has the option to read the tab delimited into excel columns.

Tab delimited is easy way to get the csv and load in excel.

If you use some tools like toad, they have facility to download directly as excel sheet or csv.

Enjoy,

Jiltin.com
Re: How to transfer data from Oracle to Excel [message #320447 is a reply to message #320418] Thu, 15 May 2008 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also use OWA_SYLK procedure.
See
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:728625409049
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:828426949078
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:769425837805
Check which version is the latest one.

Regards
Michel
Re: How to transfer data from Oracle to Excel [message #320471 is a reply to message #320427] Thu, 15 May 2008 03:55 Go to previous messageGo to next message
navaneethdba
Messages: 71
Registered: July 2007
Location: chennai
Member
Hi,

Thanks all for your replies....
Can u please explain a bit more about csv,I havent done this before...
please

TIA,
Re: How to transfer data from Oracle to Excel [message #320473 is a reply to message #320471] Thu, 15 May 2008 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CSV, comma separated value
SQL> select empno||','||ename from emp;
EMPNO||','||ENAME
----------------------------------------
7369,SMITH
7499,ALLEN
7521,WARD
7566,JONES
7654,MARTIN
7698,BLAKE
7782,CLARK
7788,SCOTT
7839,KING
7844,TURNER
7876,ADAMS
7900,JAMES
7902,FORD
7934,MILLER

14 rows selected.

Regards
Michel
Re: How to transfer data from Oracle to Excel [message #320496 is a reply to message #320473] Thu, 15 May 2008 05:11 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Alternatively, you may use the SET COLSEP SQL*Plus command, such as
SQL> set colsep ','
SQL>
SQL> spool dept.csv
SQL>
SQL> select * from dept;

        10,ACCOUNTING    ,NEW YORK
        20,RESEARCH      ,DALLAS
        30,SALES         ,CHICAGO
        40,OPERATIONS    ,BOSTON

SQL> spool off;
Re: How to transfer data from Oracle to Excel [message #320730 is a reply to message #320496] Fri, 16 May 2008 02:15 Go to previous messageGo to next message
navaneethdba
Messages: 71
Registered: July 2007
Location: chennai
Member
Hi

Thanks for your replies..... It really helped me a lot..



Re: How to transfer data from Oracle to Excel [message #321706 is a reply to message #320730] Wed, 21 May 2008 02:00 Go to previous messageGo to next message
navaneethdba
Messages: 71
Registered: July 2007
Location: chennai
Member

Hi,

I have some problem when converting to "csv" file.
I am trying to convert oracle table data into excel file and need to display in front end.

So i am trying to transfer it using Oracle. My problem is, Oracle table contains nearly 1 lakh records.
Its getting very slow to convert that much of records to excel.

Is there any other option in Oracle, which i can do it little bit faster. please Advice.....

TIA,

Re: How to transfer data from Oracle to Excel [message #321709 is a reply to message #321706] Wed, 21 May 2008 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How do you do it?

Regards
Michel
Re: How to transfer data from Oracle to Excel [message #321716 is a reply to message #321709] Wed, 21 May 2008 02:21 Go to previous messageGo to next message
navaneethdba
Messages: 71
Registered: July 2007
Location: chennai
Member
Hi Michel,

Thanks for your reply..

In frontend i dont know,Developer asked me that he need to transfer Oracle table data to excel,and he can use that excel file to display it in application.

So i am searching for methods which are faster to transfer records from Oracle to Excel.

please Advice..

TIA,
Re: How to transfer data from Oracle to Excel [message #321724 is a reply to message #321716] Wed, 21 May 2008 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i am searching for methods which are faster to transfer records from Oracle to Excel.

Faster than what?
Until you tell us how you do it, we can't say you how to do it faster.

Regards
Michel
Re: How to transfer data from Oracle to Excel [message #321738 is a reply to message #321724] Wed, 21 May 2008 03:41 Go to previous messageGo to next message
navaneethdba
Messages: 71
Registered: July 2007
Location: chennai
Member
Hi,

Am sorry,I am using it by the query which u guys have given above..

In => sqlplusw
set pages 1000
set lines 1000
set colsep ','
set feedback off
spool e:\emp.csv
select * from emp;
spool off

It takes nearly 10 mins to transfer... please Advice
Re: How to transfer data from Oracle to Excel [message #321740 is a reply to message #321738] Wed, 21 May 2008 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add "set arraysize 100", "set trimspool on", "set termout off" and execute it on server not on client.

Regards
Michel
Re: How to transfer data from Oracle to Excel [message #321760 is a reply to message #321740] Wed, 21 May 2008 04:42 Go to previous messageGo to next message
navaneethdba
Messages: 71
Registered: July 2007
Location: chennai
Member
Thanks a lot for your reply Michel....
It helps me a lot..

Thanks again
Re: How to transfer data from Oracle to Excel [message #321781 is a reply to message #321760] Wed, 21 May 2008 05:23 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It took 10 minutes.
How much time does it take now, after you did what Michel suggested?
Re: How to transfer data from Oracle to Excel [message #321782 is a reply to message #321781] Wed, 21 May 2008 05:25 Go to previous messageGo to next message
navaneethdba
Messages: 71
Registered: July 2007
Location: chennai
Member
Hi,

It took 1 min to transfer..
Re: How to transfer data from Oracle to Excel [message #321808 is a reply to message #321782] Wed, 21 May 2008 06:36 Go to previous messageGo to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
hi Michel

Quote:
Add "set arraysize 100", "set trimspool on", "set termout off" and execute it on server not on client ]


why we should not run it on client

[Updated on: Wed, 21 May 2008 06:37]

Report message to a moderator

Re: How to transfer data from Oracle to Excel [message #321815 is a reply to message #321808] Wed, 21 May 2008 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
why we should not run it on client

To avoid transmitting data through SQL*Net.
Do it in server and if you want the result on client, compress the result and ftp it.

Regards
Michel
Re: How to transfer data from Oracle to Excel [message #321821 is a reply to message #321815] Wed, 21 May 2008 07:09 Go to previous messageGo to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
Hi,
can you please tell me what is the purpose of set arraysize 200 & set termout off
Re: How to transfer data from Oracle to Excel [message #321829 is a reply to message #321821] Wed, 21 May 2008 07:34 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
An obvious response would be to search for it.
Re: How to transfer data from Oracle to Excel [message #321867 is a reply to message #321821] Wed, 21 May 2008 10:47 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
set array size: Sets the number of rows--called a batch--that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency.

set termout off: Controls the display of output generated by commands executed from a script. OFF suppresses the display so that you can spool output from a script without seeing the output on the screen.

refer to oracle documentation for more set options

http://download.oracle.com/docs/cd/B10500_01/server.920/a90842/ch13.htm#1011230
Re: How to transfer data from Oracle to Excel [message #322370 is a reply to message #320420] Fri, 23 May 2008 05:02 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Instead of CSV or any other method, is it not easier to get
the data directly in excel?

Define Oracle ODBC (from control panel)
excel->data->get external data->new database query->
Data Source Name->cancel->yes->close->
SQL->TypeYourQuery->OK->return to excel sheet.

Regards,

MSMallya
Re: How to transfer data from Oracle to Excel [message #322373 is a reply to message #322370] Fri, 23 May 2008 05:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Easier? No, not necessarily.
Most of us are not Excel-programmers. This also requires to save the Excel file as part of your code-base.
Re: How to transfer data from Oracle to Excel [message #335581 is a reply to message #320418] Tue, 22 July 2008 16:23 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
YOU CAN EASILY TRANSFER DATA TO EXCEL WITH THE HELP OF TOAD Sad
Re: How to transfer data from Oracle to Excel [message #335582 is a reply to message #335581] Tue, 22 July 2008 17:06 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you don't mind adding a piece of 3rd party software into the solution, and slowing the whole thing down by fetching all the data to the client....
Previous Topic: procedure to backup table
Next Topic: Flattening the file
Goto Forum:
  


Current Time: Sun Dec 04 14:39:07 CST 2016

Total time taken to generate the page: 0.05793 seconds