How to generate a delimiter in spool output (Excel .csv files)


In order to generate a delimited file output, you need to concatenate columns using the desired delimiter i.e. comma:

Example:
select empno|| ‘,’||ename||’&’||mgr from X;

Other option is using:
SQL> set colsep ‘,’
SQL> spool c:\testexcel.csv
SQL> select * from emp;

Change some of the default SQL*Plus parameters, that will be garbage for Excel:
feedback=off
newpage=none
termout=off
header=off

If some columns are empty, be aware to include the delimiter, too:
nvl(to_char(col2),’,’)

4 thoughts on “How to generate a delimiter in spool output (Excel .csv files)

  1. This is a really useful technique in analyzing data. I have used this technique many times in extracting data for analysis in Excel.

    I have a suggestion, if you use the pipe(|) symbol as a delimiter, you will remove the possibility of a comma in the data(e.g. in address fields) causing problems with your extract.

    Like

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s