Common Format and MIME Type for Comma-Separated Values (CSV) Files

1.1. Terminology

The key words "MUST", "MUST NOT", "REQUIRED", "SHALL", "SHALL NOT", "SHOULD", "SHOULD NOT", "RECOMMENDED", "NOT RECOMMENDED", "MAY", and "OPTIONAL" in this document are to be interpreted as described in BCP 14 [RFC2119] [RFC8174] when, and only when, they appear in all capitals, as shown here.¶

1.2. Motivation For and Status of This Document

The original motivation of [RFC4180] was to provide a reference in order to register the media type "text/csv". It tried to document existing practices at the time based on the approaches used by most implementations. This document continues to do the same, and updates the original document to reflect current practices for generating and consuming of CSV files.¶

Both [RFC4180] and this document are published as informational RFC for the benefit of the Internet community and and not intended to be used as formal standards. Implementers should consult [RFC1796] and [RFC2026] for crucial differences between IETF standards and informational RFCs.¶

2.1. High level description

1. Each record is located on a separate line, ended by a line break (CR, LF or CRLF). For example:¶

   aaa,bbb,cccCRLF
   zzz,yyy,xxxCRLF¶

2. The last record in the file MUST have an ending line break. For example:¶

   aaa,bbb,cccCRLF
   zzz,yyy,xxxCRLF¶

3. The first record in the file MAY be an optional header with the same format as normal records. This header will contain names corresponding to the fields in the file and SHOULD contain the same number of fields as the records in the rest of the file. For example:¶

   field_name_1,field_name_2,field_name_3CRLF
   aaa,bbb,cccCRLF
   zzz,yyy,xxxCRLF¶

4. Within each record, there MAY be one or more fields, separated by commas. Each record 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. For example:¶

   aaa,bbb,cccCRLF¶

5. Each field MAY be enclosed in double quotes (however some programs, do not use double quotes at all). If fields are not enclosed with double quotes, then double quotes MUST NOT appear inside the fields. For example:¶

   "aaa","bbb","ccc"CRLF
   zzz,yyy,xxxCRLF¶

6. Fields containing line breaks (CR, LF or CRLF), double quotes, or commas MUST be enclosed in double-quotes. The same applies for the first field of a record that starts with a hash to avoid the field from being parsed as a comment. For example:¶

   "aaa","b CRLF
   bb","ccc"CRLF
   zzz,yyy,xxxCRLF
   "#aaa",#bbb,cccCRLF¶

7. A double-quote appearing inside a field MUST be escaped by preceding it with another double quote. For example:¶

   "aaa","b""bb","ccc"CRLF¶

8. A hash sign MAY be used to mark lines that are meant to be commented lines. A commented line can contain any whitespace or visible character until it is terminated by a line break (CR, LF or CRLF). A comment line MAY appear in any line of the file (before or after an OPTIONAL header) but MUST NOT be mistaken with a subsequent line of a multi-line field. Subsequent lines of multi-line fields can start with a hash sign and MUST NOT interpreted as comments. For example:¶

   #commentCRLF
   aaa,bbb,cccCRLF
   #comment 2CRLF
   "aaa","this is CRLF
   # not a comment","ccc"CRLF¶

2.2. Default charset and line break values

Since the initial publication of [RFC4180], the default charset for "text/*" media types has been changed to UTF-8 (as per [RFC6657]) and [RFC7111]. This document reflects this change and the default charset for CSV files is now UTF-8.¶

Although section 4.1.1. of [RFC2046] defines CRLF to denote line breaks, implementers MAY recognize a single CR or LF as a line break (similar to section 3.1.1.3 of [RFC7231]). However, some implementations MAY use other values.¶

2.3. ABNF Grammar

The ABNF grammar (as per [RFC5234]) appears as follows:¶

file = *((comment / record) linebreak)

comment = hash *comment-data

record = first-field *(comma field)

linebreak = CR / LF / CRLF

first-field = (escaped / first-non-escaped)

field = (escaped / non-escaped)

escaped = DQUOTE *(data-with-hash / comma / CR / LF / 2DQUOTE) DQUOTE

first-non-escaped = [data *data-with-hash]

non-escaped = *data-with-hash

comma = %x2C

hash = %x23

comment-data = WSP / %x21-7E / UTF8-data
         ; characters without control characters

data = WSP / %x21 / %x24-2B / %x2D-7E / UTF8-data
         ; characters without control characters, comma, hash and DQUOTE

data-with-hash = data / hash

CR = %x0D ; as per section B.1 of [RFC5234]

DQUOTE = %x22 ; as per section B.1 of [RFC5234]

LF = %x0A ; as per section B.1 of [RFC5234]

CRLF = CR LF ; as per section B.1 of [RFC5234]

HTAB = %x09 ; as per section B.1 of [RFC5234]

SP = %x20 ; as per section B.1 of [RFC5234]

WSP = SP / HTAB ; as per section B.1 of [RFC5234]

UTF8-data = UTF8-2 / UTF8-3 / UTF8-4 ; as per section 4 of [RFC3629]

Note that the authoritative definition of UTF-8 is in section 2.5 of [UNICODE].¶

3.1. Null values

Some implementations (such as databases) treat empty fields and null values differently. For these implementations, there is a need to define a special value representing a null. However, this specification does not attempt to define a default value for nulls.¶

Example of a CSV file with nulls (if "NULL" is used to mark nulls):¶

field_name_1,field_name_2,field_name_3CRLF
aaa,bbb,cccCRLF
zzz,NULL,xxxCRLF¶

3.2. Empty files

Implementers should be aware that in accordance to this specification a file does not need to contain any comments or records. Therefore, an empty file with zero bytes is considered valid.¶

3.3. Empty lines

This specification recommends but doesn't require having the same number of fields in every line. This allows CSV files to have empty lines without any fields at all. Implementors may choose to to skip empty lines instead of parsing them but this specification does not dictate such behavior.¶

Example of a CSV file with empty lines:¶

field_name_1,field_name_2,field_name_3CRLF
aaa,bbb,cccCRLF
CRLF
zzz,yyy,xxxCRLF¶

However, if the records are only made up of one field it is not possible to differentiate between an empty line, and an empty and unquoted field. This differentiation might play an important role in some implementations such as database exports/imports.¶

Example of a CSV file with empty lines and only one field per record:¶

aaa
CRLF
bbbCRLF¶

3.4. Fields spanning multiple lines

When quoted fields are used, it is possible for a field to span multiple lines, even when line breaks appear within such field.¶

3.5. Unique header names

Implementers should be aware that some applications may treat header values as unique (either case-sensitive or case-insensitive).¶

3.6. Whitespace outside of quoted fields

When quoted fields are used, this document does not allow whitespace between double quotes and commas. Implementers should be aware that some applications may be more lenient and allow whitespace outside the double quotes.¶

3.7. Other field separators

This document defines a comma as a field separator but implementers should be aware that some applications may use different values, especially with non-English languages. Those are outside the scope of this document and implementers should consult other efforts such as [CSVW].¶

3.8. Escaping double quotes

This document prescribes that a double-quote appearing inside a field must be escaped by preceding it with another double quote. Implementers should be aware that some applications may choose to use a different escaping mechanism.¶

3.9. BOM header

Applications that create text files with unicode character encoding might write a BOM (byte order mark) header in order to support multiple unicode encodings (like UTF-16 and UTF-32). Some applications might be able to read and properly interpret such a header, others could break. Implementors should review section 6 of [RFC3629] and section 23.8 of [UNICODE].¶

3.10. Bidirectional text

While most of the world's written languages are displayed left-to-right, many languages such as ones based on Hebrew or Arabic scripts are displayed primarily right-to-left. Implementers should consult the "bidirectional display" part in section 5 of [RFC6365] for further guidance.¶

4.1. IANA Considerations

IANA is directed to update the MIME type registration for "text/csv" as per instructions provided in Section 4 of this document and include a reference to this document within the registration.¶