OraFAQ Forum: Server Utilities » load query result in ms-excel sheet

Home » RDBMS Server » Server Utilities » load query result in ms-excel sheet

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

load query result in ms-excel sheet [message #567297]

Wed, 26 September 2012 05:58

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

hello experts,

i have tried it , but it load the data in text file , but i want to load this data in excel sheet
in such a way that each column should be in different-2 cell of excel sheet.


SQL> spool on
SQL> spool 'd:\data.text'
SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     D
EPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- -----
-----
      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
   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

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     D
EPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- -----
-----
      7900 JAMES      CLERK           7698 03-DEC-81        950
   30
      7902 FORD       ANALYST         7566 03-DEC-81       3000
   20
      7934 MILLER     CLERK           7782 23-JAN-82       1300
   10

14 rows selected.

SQL> spool off;
SQL>

please tell me how to do this.

thanks in advance.......

Report message to a moderator

Re: load query result in ms-excel sheet [message #567298 is a reply to message #567297]

Wed, 26 September 2012 06:03

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

sorry to give info about the version.......

i am using oracle version 11.2.0.1 and ms excel2007

thanks again.....

Report message to a moderator

Re: load query result in ms-excel sheet [message #567305 is a reply to message #567297]

Wed, 26 September 2012 06:37

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

in such a way that each column should be in different-2 cell of excel sheet.

What does that mean?

Regards
Michel

Report message to a moderator

Re: load query result in ms-excel sheet [message #567308 is a reply to message #567305]

Wed, 26 September 2012 07:06

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

thanks for the response sir,

Michel Cadot wrote on Wed, 26 September 2012 06:37

Quote:
in such a way that each column should be in different-2 cell of excel sheet.

What does that mean?

Regards
Michel

the result of the below query is loaded in excel sheet(i changed the file type below)
but all the columns(e.g empno,ename.....) goes in one cell of excell sheet
but i need these columns in different-2 column of excel sheet.

SQL> spool on
SQL> spool 'd:\data.csv'
SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     D
EPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- -----
-----
      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
   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

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     D
EPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- -----
-----
      7900 JAMES      CLERK           7698 03-DEC-81        950
   30
      7902 FORD       ANALYST         7566 03-DEC-81       3000
   20
      7934 MILLER     CLERK           7782 23-JAN-82       1300
   10

14 rows selected.

SQL> spool off;
SQL>

so sir how should we load column data in their individual column of excell sheet.

thanks again........

Report message to a moderator

Re: load query result in ms-excel sheet [message #567314 is a reply to message #567308]

Wed, 26 September 2012 08:15

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

set colsep ';'
set ...
spool ...csv
select ...
spool off

Then when you are inside Excel, Data->Convert and follow the screens (in excel 2003, search for excel 2007, this has nothing to do with Oracle).

Regards
Michel

Report message to a moderator

Previous Topic:	Sql Loader
Next Topic:	TOP N SQL in dbms_workload_repository,

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 11 18:50:23 CST 2025