|
|
Re: How to transfer data from Oracle to Excel [message #320427 is a reply to message #320418] |
Thu, 15 May 2008 02:12   |
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 #320473 is a reply to message #320471] |
Thu, 15 May 2008 03:59   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Alternatively, you may use the SET COLSEP SQL*Plus command, such asSQL> 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 #321706 is a reply to message #320730] |
Wed, 21 May 2008 02:00   |
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 #321716 is a reply to message #321709] |
Wed, 21 May 2008 02:21   |
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 #321738 is a reply to message #321724] |
Wed, 21 May 2008 03:41   |
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 #321867 is a reply to message #321821] |
Wed, 21 May 2008 10:47   |
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   |
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
|
|
|
|
|
|