save sql results to excell file

[mgl70]

hi ,

how can we save sql results to excell file with column headings.
I know TOAD can do this. but I need at sql plus.

ex:

SET PAGESIZE 10000;
SET LINESIZE 200;
spool h1.txt
--spool h1.xls

COLUMN empno heading 'EMPLOYEENUMBER' format A15;
COLUMN decode(ename, 'SMITH','SMITH',NULL)heading 'EMPLOYEEName' format A12;


select empno || ; ||
decode(ename, 'SMITH','SMITH',NULL) || ; ||
sal
from emp;

spool off;




when am giving this. for empno column heading, it is working. in column heading for decode..., it is not formatting to EMPLOYEEName and giving concat operator(||) is giving with headings, how do I get rid of this.
When Open in excell, the headings are like this.

EMPLOYEENUMBER||' '||decode()||'



I saw sqlplus column format, but could not find the solution

I know this way.
select empno employeenum,
decode(.....) employeename.
sal salary
from emp;

but I need in excell.

Please ignore my typing mistakes in writing sql query.

Thanks for your help.

 

[Turkbear]

Hi,
Why export the headings at all?

Use the 'set heading off' option and, after exporting, add the headings yourself..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[LKBrwnDBA]

Try this.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[skunkmaster]

Not sure if I understand you fully. Are you creating a delimited file for excel (with semicolons as the delimiter)? Using the || you only have one column!

If so could try:-

SET PAGESIZE 10000
SET LINESIZE 200
spool h1.txt
--spool h1.xls

COLUMN colname heading 'EMPLOYEENUMBER;EMPLOYEENAME;SALARY' format A50;

select empno || ';' ||
       decode(ename, 'SMITH','SMITH',NULL) || ';' ||
       sal colname
 from emp;

spool off

OR

SET HEADING OFF
SET UNDERLINE OFF
SET PAGESIZE 10000
SET LINESIZE 200
spool h1.txt
--spool h1.xls

select 'EMPLOYEENUMBER;EMPLOYEENAME;SALARY' from dual;

select empno || ';' ||
       decode(ename, 'SMITH','SMITH',NULL) || ';' ||
       sal
 from emp;

spool off

or even write an html file which excel will load (need sqlplus v8.1.6 or above I believe)

SET TERM OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET MARKUP HTML ON SPOOL ON HEAD " " -
BODY "TEXT='#000000'" -
TABLE "WIDTH='50%' BORDER='0'"
SPOOL h1.xls

COLUMN empno heading 'EMPLOYEE NUMBER' format 999999;
COLUMN ename heading 'EMPLOYEE NAME' format A30;
COLUMN sal heading 'SALARY' format 999999;

select empno,
       decode(ename, 'SMITH','SMITH',NULL) ename,
       sal
 from emp;

SET MARKUP HTML OFF
SPOOL OFF

Good Luck
Kev