Home » SQL & PL/SQL » SQL & PL/SQL » creating a excel file data from the sql prompt (oracle 10g)
creating a excel file data from the sql prompt [message #330385] Mon, 30 June 2008 01:05 Go to next message
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 #330386 is a reply to message #330385] Mon, 30 June 2008 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "excel" you will find many answers.
Some for "read" others for "write", so don't stop at the first one.

Regards
Michel
Re: creating a excel file data from the sql prompt [message #330395 is a reply to message #330385] Mon, 30 June 2008 01:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Excel can read comma separated files (.csv)
Now think of a way to generate those from sql.
Re: creating a excel file data from the sql prompt [message #330412 is a reply to message #330395] Mon, 30 June 2008 02:17 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
I am new to this concept pls provide the steps how to execute the above task

Thanks,
Re: creating a excel file data from the sql prompt [message #330413 is a reply to message #330412] Mon, 30 June 2008 02:20 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
How will you select columns seperated via comma in Sql.

Regards,
Rajat Ratewal
Re: creating a excel file data from the sql prompt [message #330416 is a reply to message #330413] Mon, 30 June 2008 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set colsep ','

Regards
Michel
Re: creating a excel file data from the sql prompt [message #330441 is a reply to message #330416] Mon, 30 June 2008 03:26 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Use '||' operator to select values of fields seperated via colsep ',' i.e.

Spool c:\Test.csv;

select column1||','||column2....||','||columnn from <table_name>;

Spool off;


Regards,
Rajat Ratewal
Re: creating a excel file data from the sql prompt [message #330458 is a reply to message #330441] Mon, 30 June 2008 03:59 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 #330472 is a reply to message #330458] Mon, 30 June 2008 04:30 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
U have metioned an utl_file,can i know which utl_file is used for this concept ,if means provide the details

Thanks,
Re: creating a excel file data from the sql prompt [message #330474 is a reply to message #330471] Mon, 30 June 2008 04:34 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Use Some other concatenation character

Create a text file instead of csv.

i.e. instead of ',' you can use any other character like ':' or '$'.

And then export this file in Excel using

Data->Get External Data->Export Text File

Regards,
rajat

Re: creating a excel file data from the sql prompt [message #330475 is a reply to message #330474] Mon, 30 June 2008 04:36 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Sorry i mean import that file Import Text File.

Regards,
Rajat
Re: creating a excel file data from the sql prompt [message #330476 is a reply to message #330472] Mon, 30 June 2008 04:44 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
UTL_FILE is a package used to work with OS level files..please search on it and you will get more details...
Re: creating a excel file data from the sql prompt [message #330477 is a reply to message #330471] Mon, 30 June 2008 04:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set linesize 32000
set trimout on
set trimspool on

Regards
Michel
Re: creating a excel file data from the sql prompt [message #330489 is a reply to message #330477] Mon, 30 June 2008 05:04 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi michel,
It works fine thanks a lot guys for your kind replys,
but my question reg this

1.Whether the set linsize can be increased (ie can we increase more the given size u mentioned)?

Thanks,
Re: creating a excel file data from the sql prompt [message #330498 is a reply to message #330489] Mon, 30 June 2008 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As always just try it:
SQL> set linesize 10000000
SP2-0267: linesize option 10000000 out of range (1 through 32767)

Regards
Michel
Re: creating a excel file data from the sql prompt [message #352578 is a reply to message #330458] Wed, 08 October 2008 07:34 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: ORA-30926: unable to get a stable set of rows in the source tables
Next Topic: ORA-22992:
Goto Forum:
  


Current Time: Sat Dec 03 14:15:45 CST 2016

Total time taken to generate the page: 0.09954 seconds