Solved

in oracle 10g how to convert  the output of ' select * from emp' to excel file

Posted on 2014-11-22
7
55 Views
Last Modified: 2016-06-11
in oracle 10g how to convert  the output of ' select * from emp' to excel file
0
Comment
Question by:priya saxena
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40459099
Are you using sqlplus or some graphical development environment? On which OS?
0
 
LVL 38

Accepted Solution

by:
Gerwin Jansen, EE MVE earned 168 total points
ID: 40459385
If you are using sqlplus, you can do this to create a csv file that can be opened by Excel:

spool file.csv
select column1 || ',' ||  column2 ',' || column3
from emp;
spool off

You need to specify every column you want in the above select statement. If you have columns that contain spaces, you could add "" - like this:
(column2 is the one with spaces)

select column1 || ',"' ||  column2 '",' || column3

You can add some settings after the first spool command to get nice output:

set headsep off
set echo off
set feedback off
set linesize 1000
set pagesize 0
set trimspool on
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 166 total points
ID: 40459439
I normally find that for a simple query that it's easier to open Excel and import directly from MS Query.   You launch Excel, Select Data / Import External Data and then you can connect to an ODBC Data Source via the wizard that launches and it will guide you through.

Alternatively you can also use a tool Oracle SQL Developer (free)  or a paid tool like PL/SQL Developer to run the query and then export the result grid directly to Excel.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 166 total points
ID: 40462760
When using SQL*Plus, you can create XML files which are easily imported into Excel.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41649006
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 41649007
There are answers given here, I suggest as solution https:#a40459385 with assist for https:#40459439 and https:#a40462760
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question