creating a excel file data from the sql prompt [message #330385] |
Mon, 30 June 2008 01:05  |
ram anand
Messages: 244 Registered: February 2008 Location: india
|
Senior Member |
|
|
Hi,
Is there is possiblity of creating a excel file from the select query in sql prompt
for ex;
SQL> select * from emp
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
Here i need this 14 rows to be saved as excel file without any third party tool,so pls help me reg this
Thanks,
|
|
|
|
|
|
|
|
|
Re: creating a excel file data from the sql prompt [message #330458 is a reply to message #330441] |
Mon, 30 June 2008 03:59   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
rajatratewal wrote on Mon, 30 June 2008 10:26 | Use '||' operator to select values of fields seperated via colsep ','
|
Now you're mixing up two different solutions.
Setting colsep=',' means you can select individual columns, and sqlplus will separate those with a comma, instead of a space in the output.
Concatenating is the way to go for more fancy stuff, or if you don't spool / use sqlplus but use utl_file from within a stored procedure.
|
|
|
Re: creating a excel file data from the sql prompt [message #330471 is a reply to message #330458] |
Mon, 30 June 2008 04:27   |
ram anand
Messages: 244 Registered: February 2008 Location: india
|
Senior Member |
|
|
Hi,
The above query have worked fine for particular case
but in another case
case1:
SQL> spool d:\test1.csv
SQL> select empno||','||ename||','||job from emp;
EMPNO||','||ENAME||','||JOB
-------------------------------------------------------------
7369,SMITH,CLERK
7499,ALLEN,SALESMAN
7521,WARD,SALESMAN
7566,JONES,MANAGER
7654,MARTIN,SALESMAN
7698,BLAKE,MANAGER
7782,CLARK,MANAGER
7788,SCOTT,ANALYST
7839,KING,PRESIDENT
7844,TURNER,SALESMAN
7876,ADAMS,CLERK
EMPNO||','||ENAME||','||JOB
-------------------------------------------------------------
7900,JAMES,CLERK
7902,FORD,ANALYST
7934,MILLER,CLERK
14 rows selected.
case2
sql>spool d:\test.csv
sql>select error_number||','||error_key||','||error_msg from error_messages
ERROR_NUMBER||','||ERROR_KEY||','||ERROR_MESSAGE
--------------------------------------------------------------------------------
ier_Code. Alternate_Capacity found
20100,XOVALTERNATE_CAPACITY,Alternate_Capacity Period Overlap
1,XPKALTERNATE_CAPACITY_DTL,Alternate_Capacity_Dtl already exists
2291,XFKALTCAP_ALTCAPDTL,Foreign Key Violated in Alternate_Capacity_Dtl - Altern
ate_Capacity_Key. Alternate_Capacity not found
2291,XFKITM_ALTCAPDTL,Foreign Key Violated in Alternate_Capacity_Dtl - item_type
. Item_Type not found
2292,XFKALTCAP_ALTCAPDTL,Foreign Key Violated in Alternate_Capacity_Dtl - Altern
So in case2 the datas get into a new line creating a another column so is there is any way to clear the issue and make datas to be displayed as in case1 ,if means pls can anyone provide me the necessary steps
Thanks,
|
|
|
|
|
|
|
|
|
|
Re: creating a excel file data from the sql prompt [message #352578 is a reply to message #330458] |
Wed, 08 October 2008 07:34   |
rjlyders
Messages: 1 Registered: October 2008
|
Junior Member |
|
|
Is there a way to get variable column width output when using the SQL*Plus COLSEP command?
Oracle's docs show that the SQL*Plus COLSEP command still results in fixed-width columns which isn't what is desired when trying to create a data extract for Excel. I always simply concatentate the columns together to get the desired variable width output such as
select object_name || chr(9) || object_type
from all_objects
where rownum<5
PS: Interestingly enough, the following Oracle tip shows variable output when using COLSEP:
http://www.oracle.com/technology/oramag/code/tips2004/051704.html
When you take the above example code and paste it directly into SQL*Plus it does not produce variable width columns, but rather it produces fixed-width columns as per the Oracle documentation.
For now, I will just stick to concatenating columns together to get variable width output.
-Rich
|
|
|
Re: creating a excel file data from the sql prompt [message #352579 is a reply to message #352578] |
Wed, 08 October 2008 07:51  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
The tip of Oracle magazine does not show the real output. but one you get when you copy and paste from a html page where spaces are shrinked to 1.
You can also see that tabulations are not tabulations but spaces. It is not a real output.
Regards
Michel
|
|
|