Exporting Oracle table to Excel/CSV files - Simple tip

Exporting Data to CSV file or MS Excel

                            One of the most cumbersome tasks that every DBA experience is getting the data in a readable format from the query output when we are abandoned with just the basic SQLPLUS.
If we think of connecting the Oracle database from Excel , it involves another major task of configuring the ODBC. Phut! That always take huge time and I always look for an effective yet simple alternative!
The native SQLPLUS has wide range of formatting options, the only thing we need is , to explore.The very simple way of doing that is using the colsep formatting option.

The below example will help you understand better.
Create an SQL file with the below :

conn username/pass@DBNAME
Set pages 2000 lines 300
Set colsep , — this does the trick here
Spool filename.csv
Select * from table_name;
Spool off
exit

Now run the SQL file by invoking sqlplus.
Note: If you are using Linux servers, you can use the below:

nohup sqlplus /nolog @sqlfile.sql &

This command runs the sqlfile in the background , so that the query will be running. Even if your current Unix session expires or timed out. You can now get the filename.csv file in the directory where you invoked the sqlplus command.
Oh yeah! You got that right and simple simultaneously!!

You can either transfer the file to your local or email it using the below command.

echo "" | mailx -s "CSV output of table" -a filename.csv name@domain.com

I am not really sure of the windows equivalent of running commands in the background and email functionality, will check and get back to you if I find something.

This simple hack will save a lot of time in formatting from sqlplus when you are left without any great gui tools like SQLDeveloper or TOAD,  for database connection.

Hope that helps!!