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.
Maria TorresData AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.