Columns to String

Comma Separated Values (CSV)

To create a string of comma delimited values from a data set that looks like this

VAL_1  VAL_2  VAL_3  NOTE
------ ------ ------ -------------------------
a                    One NOT NULL, Two NULL
       b             One NOT NULL, Two NULL
              c      One NOT NULL, Two NULL
a      b             Two NOT NULL, One NULL
a      b      c      Three NOT NULL, Zero NULL
                     Zero NOT NULL, Three NULL
 

simply use the concatenation operator, "||", as in the following snippet.

select
  val_1, val_2, val_3,
  val_1 || ',' || val_2 || ',' || val_3 as string
from t
order by key ;
 
VAL_1  VAL_2  VAL_3  STRING
------ ------ ------ --------------------
a                    a,,
       b             ,b,
              c      ,,c
a      b             a,b,
a      b      c      a,b,c
                     ,,
 

To exclude null values from the end result LTRIM and NVL2 can be used.

select
  val_1, val_2, val_3,
  LTRIM
  ( val_1 ||
      NVL2( val_2, ',' || val_2 , null ) ||
      NVL2( val_3, ',' || val_3 , null ) ,
    ','
  ) as string
from t
order by key ;
 
VAL_1  VAL_2  VAL_3  STRING
------ ------ ------ --------------------
a                    a
       b             b
              c      c
a      b             a,b
a      b      c      a,b,c
 

How to Create a CSV File

While there is no official standard for CSV file formats, RFC 4180 - Common Format and MIME Type for Comma-Separated Values (CSV) Files describes one commonly used set of rules. We will follow this standard in the examples to follow.

When writing a query whose output will be spooled to a CSV file we need to disable a few SQL*Plus features to ensure they do not corrupt the contents. Here are the settings I typically use.

set ECHO      off
set FEEDBACK  off
set LINESIZE  60
set RECSEP    off
set TRIMSPOOL on
set VERIFY    off
set PAGESIZE  0

spool temp.txt replace
select
  val_1 || ',' || val_2 || ',' || val_3 as string
from t
order by key ;
 
a,,
,b,
,,c
a,b,
a,b,c
,,
 
spool off
 

The value for LINESIZE should be larger than the longest expected line in the CSV file.

How to Escape Double Quotes, CR, and LF

Rules for escaping special characters vary from product to product. For products that follow RFC 4180 the following rules affect how we write our query.

  • "4. Within the header and each record, there may be one or more fields, separated by commas. Each line should contain the same number of fields throughout the file. Spaces are considered part of a field and should not be ignored. The last field in the record must not be followed by a comma."

  • "5. Each field may or may not be enclosed in double quotes" - we will enclose all fields in double quotes since it simplifies our query

  • "6. Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes" - since we will enclose all fields in double quotes we automatically obey this rule

  • "7. If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote."

Before we see the solution let's create some test data with special characters first. The NOTE column will contain values with double quotes, commas, and a line feed.

insert into t values ( 'String 7', 'd', 'e', 'f', 'line 1' || chr(10) || 'line 2' );
insert into t values ( 'String 8', 'g', 'h', 'i', '"1", "2", or "3"' );
commit;

set pagesize 60

select val_1, val_2, val_3, note
from t
order by key ;
 
VAL_1  VAL_2  VAL_3  NOTE
------ ------ ------ -------------------------
a                    One NOT NULL, Two NULL
       b             One NOT NULL, Two NULL
              c      One NOT NULL, Two NULL
a      b             Two NOT NULL, One NULL
a      b      c      Three NOT NULL, Zero NULL
                     Zero NOT NULL, Three NULL
d      e      f      line 1
                     line 2
g      h      i      "1", "2", or "3"
 

Now here is the query.

set echo      off
set feedback  off
set linesize  60
set recsep    off
set trimspool on
set verify    off

set pagesize  0

select
  '"' || replace( val_1, '"', '""' ) || '"'
    || ',"' || replace( val_2, '"', '""' ) || '"'
    || ',"' || replace( val_3, '"', '""' ) || '"'
    || ',"' || replace( note , '"', '""' ) || '"'
    as string
from t
order by key ;
 
"a","","","One NOT NULL, Two NULL"
"","b","","One NOT NULL, Two NULL"
"","","c","One NOT NULL, Two NULL"
"a","b","","Two NOT NULL, One NULL"
"a","b","c","Three NOT NULL, Zero NULL"
"","","","Zero NOT NULL, Three NULL"
"d","e","f","line 1
line 2"
"g","h","i","""1"", ""2"", or ""3"""
 

How to Include a Header Line

RFC 4180 states

"3. There maybe an optional header line appearing as the first line of the file with the same format as normal record lines."

Including such a header line is easily accomplished in SQL*Plus for files with less than 50,000 lines (or whatever the maximum setting for PAGESIZE is in your environment) like this.

set echo      off
set feedback  off
set linesize  60
set recsep    off
set trimspool on
set verify    off

set pagesize  50000
set newpage   none
set heading   on
set underline off

column string heading '"Val 1","Val 2","Val 3","Note Field"'

select
  '"' || replace( val_1, '"', '""' ) || '"'
    || ',"' || replace( val_2, '"', '""' ) || '"'
    || ',"' || replace( val_3, '"', '""' ) || '"'
    || ',"' || replace( note , '"', '""' ) || '"'
    as string
from t
order by key ;
 
"Val 1","Val 2","Val 3","Note Field"
"a","","","One NOT NULL, Two NULL"
"","b","","One NOT NULL, Two NULL"
"","","c","One NOT NULL, Two NULL"
"a","b","","Two NOT NULL, One NULL"
"a","b","c","Three NOT NULL, Zero NULL"
"","","","Zero NOT NULL, Three NULL"
"d","e","f","line 1
line 2"
"g","h","i","""1"", ""2"", or ""3"""
 

Unlike column names, note how the column heading can be greater than 30 characters. In the last example the column heading for STRING is 36 characters long.

For files that will contain more than 50,000 lines I use the following approach.

set echo      off
set feedback  off
set linesize  60
set recsep    off
set trimspool on
set verify    off

set pagesize 0

select
  string
from
(
  select
    0   as sort_key_1 ,
    ' ' as sort_key_2 ,
    '"Val 1","Val 2","Val 3","Note Field"' as string
  from dual
  union all
  select
    1 as sort_key_1 ,
    key as sort_key_2 ,
    '"' || replace( val_1, '"', '""' ) || '"'
      || ',"' || replace( val_2, '"', '""' ) || '"'
      || ',"' || replace( val_3, '"', '""' ) || '"'
      || ',"' || replace( note , '"', '""' ) || '"'
      as string
  from t
)
order by
  sort_key_1 ,
  sort_key_2
;
 
"Val 1","Val 2","Val 3","Note Field"
"a","","","One NOT NULL, Two NULL"
"","b","","One NOT NULL, Two NULL"
"","","c","One NOT NULL, Two NULL"
"a","b","","Two NOT NULL, One NULL"
"a","b","c","Three NOT NULL, Zero NULL"
"","","","Zero NOT NULL, Three NULL"
"d","e","f","line 1
line 2"
"g","h","i","""1"", ""2"", or ""3"""
 

Gotcha

When creating large CSV files destined for Microsoft Excel beware of limits on the maximum number of rows per worksheet (1M rows in Excel 2007, 64K rows in earlier versions).

Cleanup

delete from t where key in ( 'String 7', 'String 8' ) ;
commit ;
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11589.html]SQL Snippets: Columns to String - Comma Separated Values (CSV)[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11589.html">SQL Snippets: Columns to String - Comma Separated Values (CSV)</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Columns to String - Comma Separated Values (CSV)
  • URL (href): http://www.sqlsnippets.com/en/topic-11589.html

Revision Notes

Date Category Note
2008-03-28 Updated Tutorial The "Columns to String: Comma Separated Values (CSV)" tutorial now includes three new sections, "How to Create a CSV File", "How to Escape Double Quotes, CR, and LF", and "How to Include a Header Line".
2009-06-24 Revision Updated note re. Excel row limits.
2007-04-11 Revision Moved topic from old parent topic-11578 to new parent topic-12118.