Results 1 to 2 of 2

Thread: Oracle to Excel

  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    1

    Thumbs up Unanswered: Oracle to Excel

    Hi,
    I want to transfer the data of a Oracle report to Microsoft Excel while the report is viewed by the user. Please tell me how to do that..

    Pranit..

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Oracle to Excel

    Simplest way to to spool the report to a file in CSV format like this:

    SQL> set linesize 1000
    SQL> set pagesize 0
    SQL> set trimspool on
    SQL> select empno||',"'||ename||'",'||deptno
    2 from emp
    3
    SQL> spool emp.csv
    SQL> /
    7369,"SMITH",20
    7499,"ALLEN",30
    7521,"WARD",30
    7566,"JONES",20
    7654,"MARTIN",30
    7698,"BLAKE",30
    7782,"CLARK",10
    7788,"SCOTT",20
    7839,"KING",10
    7844,"TURNER",30
    7876,"ADAMS",20
    7900,"JAMES",30
    7902,"FORD",20
    7934,"MILLER",10

    14 rows selected.

    SQL> spool off

    Then load that file into Excel as a CSV file. It is a good idea to enclose text items in double quotes as I did with ENAME above, in case the text includes any commas. You may also need a few more SET commands to get rid of unwanted output, e.g. SET FEEDBACK OFF.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •