A comma-separated values file stores tabular data in plain-text form.
Plain text means that the file is a sequence of characters, with no data that has to be
interpreted instead, as binary numbers.
A CSV file consists of any number of records, separated by line breaks of some kind; each
record consists of fields, separated by some other character or string, most commonly a
literal comma or tab.
Usually, all records have an identical sequence of fields.
A general standard for the CSV file format does not exist, but RFC 4180 provides a de
facto standard for some aspects of it.
Usage CSV is a common, relatively simple file format
that is widely supported by consumer, business, and scientific applications.
Among its most common uses is moving tabular data between programs that natively operate
on incompatible formats.
This works because so many programs support some variation of CSV at least as an alternative
import/export format.
For example, a user may need to transfer information from a database program that stores data in
a proprietary format, to a spreadsheet that uses a completely different format.
The database program most likely can export its data as "CSV"; the exported CSV file can
then be imported by the spreadsheet program.
"CSV" is not a single, well-defined format.
Rather, in practice the term "CSV" refers to any file that:
is plain text using a character set such as ASCII, Unicode, EBCDIC, or Shift JIS,
consists of records, with the records divided into fields separated
by delimiters, where every record has the same sequence of
fields.
Within these general constraints, many variations are in use.
Therefore "CSV" files are not entirely portable.
Nevertheless, the variations are fairly small, and many implementations allow users to preview
the first few lines of the file, and then specify the delimiter character(s), quoting
rules, etc.
If a particular CSV file's variations fall outside what a particular receiving program
supports, it is often feasible to examine and edit the file by hand or write a script
or program to fix the problem.
History Comma-separated values is a data format that
pre-dates personal computers by more than a decade: the IBM Fortran compiler under OS/360
supported them in 1967.
List-directed input/output was defined in FORTRAN 77.
List-directed input used commas and/or spaces for delimiters, so unquoted character strings
could not contain commas or spaces.
Comma-separated value lists are easier to type than fixed-column-aligned data, and were
less prone to producing incorrect results if a value was punched one column off from
its intended location.
Comma separated lists are used for the interchange of database information between machines of
two different architectures.
The plain-text character of CSV files largely avoids incompatibilities such as byte-order
and word size.
The files are largely human-readable, so it is easier to deal with them in the absence
of perfect documentation or communication.
General functionality CSV formats are best used to represent sets
or sequences of records in which each record has an identical list of fields.
This corresponds to a single relation in a relational database, or to data in a typical
spreadsheet.
CSV formats are not limited to a particular character set.
They work just as well with Unicode as with ASCII.
CSV files normally will even survive naive translation from one character set to another.
CSV does not, however, provide any way to indicate what character set is in use, so
that must be communicated separately, or determined at the receiving end.
Databases that include multiple relations cannot be exported as a single CSV file as
described here.
At best, more notational conventions must be added, for example to identify and separate
the different relations.
Such notations are not difficult to design or implement, but there is no consensus on
them and consequently very little portability.
Similarly, CSV cannot naturally represent hierarchical or object-oriented databases
or other data.
This is because every CSV record is expected to have the same structure.
CSV is therefore rarely appropriate for documents such as those created with HTML, XML, or other
markup or word-processing technologies.
Statistical databases in various fields often have a generally relation-like structure,
but with some repeatable groups of fields.
For example, health databases such as the Demographic and Health Survey typically repeat
some questions for each child of a given parent.
Statistical analysis systems often include utilities that can "rotate" such data; for
example, a "parent" record that includes information about five children can be split into five
separate records, each containing the information on one child, and a copy of all the non-child-specific
information.
CSV can represent either the "vertical" or "horizontal" form of such data.
In a relational database, similar issues are readily handled by creating a separate relation
for each such group, and connecting "child" records to the related "parent" records using
a foreign key.
In markup languages such as XML, such groups are typically enclosed within a parent element
and repeated as necessary.
With CSV there is no widely accepted single-file solution.
Lack of a standard The name "CSV" indicates the use of the comma
to separate data fields.
Nevertheless, the term "CSV" is widely used to refer a large family of formats, which
differ in many ways.
For example, many so-called "CSV" files in fact use the tab character instead of comma;
some implementations allow or require single or double quotation marks around some or all
fields; and some reserve the very first record as a header containing a list of field names.
The character set being used is undefined: some applications require a Unicode BOM to
enforce Unicode interpretation Other implementation differences include handling
of more commonplace field separators and newline characters inside text fields.
One more subtlety is the interpretation of a blank line: it can equally be the result
of writing a record of zero fields, or a record of one field of zero length; thus decoding
it is ambiguous.
Reliance on the standard documented by RFC 4180 can simplify CSV exchange.
However, this standard only specifies handling of text-based fields.
Interpretation of the text of each field is still application-specific.
In particular, there is no standard on how to represent decimal numbers, although they
are commonly embedded in CSV data, and some countries use a period as the decimal separator
and some use a comma.
E.g., a French CSV file might write pi as 3,14159.
A common solution to interoperability is to rely on human intervention: because CSV files
are plain text, humans can easily view and diagnose most issues using a text editor.
Toward standardization The huge variety among "CSV" formats has led
to the assertion that there is no "CSV standard".
In common usage, almost any delimiter-separated text data may be referred to as a "CSV" file.
Different CSV formats may not be compatible.
Nevertheless, RFC 4180 is an effort to formalize CSV.
It defines the MIME type "text/csv", and CSV files that follow its rules should be very
widely portable.
Among its requirements: DOS-style lines that end with characters
An optional header record.
Each record "should" contain the same number of comma-separated fields.
Any field may be quoted.
Fields containing a line-break, double-quote, and/or commas should be quoted..
A quote character in a field must be represented by two quote characters.
The format is simple and can be processed by most programs that claim to read CSV files.
The exceptions are programs may not support line-breaks within quoted fields, and programs
may confuse the optional header with data or interpret the first data line as an optional
header.
Technical background The format dates back to the early days of
business computing and is widely used to pass data between computers with different internal
word sizes, data formatting needs, and so forth.
For this reason, CSV files are common on all computer platforms.
CSV is a delimited text file that uses a comma to separate values.
Simple CSV implementations may prohibit field values that contain a comma or other special
characters such as newlines.
More sophisticated CSV implementations permit them, often by requiring " characters around
values that contain reserved characters.
Embedded double quote characters may then be represented by a pair of consecutive double
quotes, or by prefixing an escape character such as a backslash.
In computer science terms, a CSV file may be considered a "flat file".
Basic rules and examples Many informal documents exist that describe
"CSV" formats.
IETF RFC 4180 defines the format for the "text/csv" MIME type registered with the IANA.
Another relevant specification is provided by Fielded Text.
Creativyst provides an overview of the variations used in the most widely used applications
and explains how CSV can best be used and supported.
Rules typical of these and other "CSV" specifications and implementations are as follow:
CSV is a delimited data format that has fields/columns separated by the comma character and records/rows
terminated by newlines.
A CSV file does not require a specific character encoding, byte order, or line terminator format.
A record ends at a line terminator.
However, line-terminators can be embedded as data within fields, so software must recognize
quoted line-separators in order to correctly assemble an entire record from perhaps multiple
lines.
All records should have the same number of fields, in the same order.
Data within fields is interpreted as a sequence of characters, not as a sequence of bits or
bytes.
For example, the numeric quantity 65535 may be represented as the 5 ASCII characters "65535";
but not as a sequence of 2 bytes intended to be treated as a single binary integer rather
than as two characters.
If this "plain text" convention is not followed, then the CSV file no longer contains sufficient
information to interpret it correctly, the CSV file will not likely survive transmission
across differing computer architectures, and will not conform to the text/csv MIME type.
Adjacent fields must be separated by a single comma.
However, "CSV" formats vary greatly in this choice of separator character.
In particular, in locales where the comma is used as a decimal separator, semicolon,
TAB, or other characters are used instead.
1997,Ford,E350
Any field may be quoted.
Some fields must be quoted, as specified in following rules.
"1997","Ford","E350"
Fields with embedded commas or double-quote characters must be quoted.
1997,Ford,E350,"Super, luxurious truck"
Each of the embedded double-quote characters must be represented by a pair of double-quote
characters.
1997,Ford,E350,"Super, ""luxurious"" truck"
Fields with embedded line breaks must be quoted.
1997,Ford,E350,"Go get one now they are going fast"
In some CSV implementations, leading and trailing spaces and tabs are trimmed.
This practice is controversial, and does not accord with RFC 4180, which states "Spaces
are considered part of a field and should not be ignored.".
1997, Ford, E350 not same as
1997,Ford,E350
According to the grammar in RFC 4180, spaces outside of quotes in a field are not allowed;
however, the RFC also says that "Spaces are considered part of a field and should not
be ignored."
and "Implementors should 'be conservative in what you do, be liberal in what you accept
from others' when processing CSV files."
1997, "Ford" ,E350
In CSV implementations that do trim leading or trailing spaces, fields with such spaces
as meaningful data must be quoted.
1997,Ford,E350," Super luxurious truck "
The first record may be a "header", which contains column names in each of the fields.
Year,Make,Model 1997,Ford,E350
2000,Mercury,Cougar
Example The above table of data may be represented
in CSV format
as follows:
Year,Make,Model,Description,Price 1997,Ford,E350,"ac, abs, moon",3000.00
1999,Chevy,"Venture ""Extended Edition""","",4900.00 1999,Chevy,"Venture ""Extended Edition, Very
Large""",,5000.00 1996,Jeep,Grand Cherokee,"MUST SELL!
air, moon roof, loaded",4799.00
Example of a USA/UK CSV file:
Year,Make,Model,Length 1997,Ford,E350,2.34
2000,Mercury,Cougar,2.38
Example of an analogous German and Dutch CSV/DSV file:
Year;Make;Model;Length 1997;Ford;E350;2,34
2000;Mercury;Cougar;2,38
The latter format is not RFC 4180 compliant.
Compliance could be achieved by the use of a comma instead of a semicolon as a separator
and either the international notation for the representation of the decimal mark or
the practice of quoting all numbers that have a decimal mark.
Application support
The CSV file format is very simple and supported by almost all spreadsheets and database management
systems.
Many programming languages have libraries available that support CSV files.
Many implementations support changing the field-separator character and some quoting
conventions, although it is safest to use the simplest conventions, to maximize the
recipients' chances of handling the data.
Microsoft Excel will open .csv files, but depending on the system's regional settings,
it may expect a semicolon as a separator instead of a comma, since in some languages the comma
is used as the decimal separator.
Also, many regional versions of Excel will not be able to deal with Unicode in CSV.
One simple solution when encountering such difficulties is to change the filename extension
from .csv to .txt; then opening the file from an already running Excel instance with the
"Open" command.
OpenOffice.org Calc and LibreOffice Calc handle CSV files and pasted text with a Text Import
dialog asking the user to manually specify the delimiters, encoding, format of columns,
etc.
There are many utility programs on Unix-style systems that can deal with at least some CSV
files.
Many such utilities have a way to change the delimiter character, but lack support for
any other variations.
Some of the useful programs are: column
cut paste
join sort
uniq emacs[1]
See also Comparison of data serialization formats
CSV application support Delimiter-separated values
Fielded text Tab-separated values
References
Creativyst, How To: The Comma Separated Value File Format, creativyst.com, retrieved May
24, 2010 Shafranovich, Y., Common Format and MIME Type
for Comma-Separated Values Files, The Internet Society, RFC 4180
External links Introduction to the CSV Format
CSV-1203 File Format Specification for Business-to-Business applications
How To: The Comma Separated Value File Format RFC 4180 – RFC-Specification of the CSV
file format