• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 39
  • Last Modified:

How to export data of Oracle 9i table to excel or text file?

I'm trying to export the data contents of an Oracle 9i table to excel, but not having any success.  Several of the articles that I read on the web, suggest using certain tools which we do not have.  Is there a script that I can run that would allow me to export the data in a specific order?  If so, can you provide me with the code snippet?

Thank you.
0
Maria Torres
Asked:
Maria Torres
1 Solution
 
OMC2000Commented:
You could download Oracle SQL Developer for higher version. It should work with Oracle 9i anyway. And it supports export to Excel.

Also you could spool SQLPLUS output to CSV file

set linesize 9999
set colsep ,
set trimspool ON
set pagesize 50000
spool myfile.csv

select * from table_name;

spool off

Open in new window


and also, you could format your output as you like

select a from 
(select nvl(col1,'-')||';'||col2||';'||col3||';'||col4||';' as a
from  table_name
);

Open in new window


then you don't need colsep specification.
0
 
slightwv (䄆 Netminder) Commented:
Don't use COLSEP.  There are problems with it when sqlplus wraps columns.

Use string concatenation mentioned above.  That will work until all the columns concatenated exceed 4000 characters.
0
 
Mark GeerlingsDatabase AdministratorCommented:
You don't need to download SQL Developer (or another tool) unless you want to.  Every Oracle database includes SQL*Plus on the database server.  So, if you have access to the database server you can simply run queries like the ones suggested by OMC2000 in SQL*Plus directly on your database server.  Then you copy the resulting output file to a computer that has Excel on it, and open the file with Excel.  Excel can open and convert the ASCII file from SQL*Plus into an Excel file.

And yes, you can have this data exported in any order that you want.  Simply add an "order by" clause to your queries like this:

select * from [table_name]
order by [column1, column2, etc.];

or:
select [column1], [column2], [column3], ...
from [table_name]
order by [column1, column2, etc.];
0
 
Maria TorresData AnalystAuthor Commented:
Thank you .  I've been trying to solve this problem of  downloading data to a flat file for several days now, but to no avail.  Your code snippet helped me tremendously.  I now can move on to the next stage.  Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now