Working with CSV files

Rule 1

1. Each record is located on a separate line, delimited by a line break (CRLF). For example:
		
		   aaa,bbb,ccc CRLF
		   zzz,yyy,xxx CRLF

Rule 2

2. The last record in the file may or may not have an ending line break. For example:
		
		   aaa,bbb,ccc CRLF
		   zzz,yyy,xxx

Rule 3

3. There maybe an optional header line appearing as the first line of the file with the same format as normal record lines.  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 (the presence or absence of the header line      should be indicated via the optional "header" parameter of this MIME type).  For example:

		   field_name,field_name,field_name CRLF
		   aaa,bbb,ccc CRLF
		   zzz,yyy,xxx CRLF

Rule 4

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.  For example:

			aaa,bbb,ccc

Rule 5

5. Each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes at all).  If fields are not enclosed with double quotes, then double quotes may not     appear inside the fields.  For example:

		   "aaa","bbb","ccc" CRLF
		   zzz,yyy,xxx

Rule 6

6. Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.  For  example:

		   "aaa","b CRLF
		   bb","ccc" CRLF
		   zzz,yyy,xxx

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.  For example:

			"aaa","b""bb","ccc"

• Each record is one line - Line separator may be LF (0x0A) or CRLF (0x0D0A), a line separator may also be embedded in the data (making a record more than one line but still acceptable).
• Fields are separated with commas.
• Leading and trailing whitespace is ignored - Unless the field is delimited with double-quotes in that case the whitespace is preserved.
• Embedded commas - Field must be delimited with double-quotes.
• Embedded double-quotes - Embedded double-quote characters must be doubled, and the field must be delimited with double-quotes.
• Embedded line-breaks - Fields must be surrounded by double-quotes.
• Always Delimiting - Fields may always be delimited with double quotes, the delimiters will be parsed and discarded by the reading applications.

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 (where the decimal separator is a period/full stop and the value separator is a comma):

Year,Make,Model,Length
1997,Ford,E350,2.34
2000,Mercury,Cougar,2.38

Example of an analogous German and Dutch CSV/DSV file (where the decimal separator is a comma and the value separator is a semicolon):

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.

Are there data limits on CSV files when working with Excel?

Yes, you can import or export up to 1,048,576 rows and 16,384 columns.

CSV Vs. XML format

XML (Extensible Markup Language) is a markup language that defines a set of rules for encoding documents in a format that is both human and machine readable.

It's mostly used to transmit data across the internet between applications and is used in many API's.

In advantage over CSV, XML has a cleary defined format.

CSV does have one advantage over XML for specification in new designs. CSV has much lower overhead, thereby using much less bandwidth and storage than XML.

For new designs where overhead issues have high importance or criticality, CSV, or a more robust low-bandwidth alternative, may be the better choice.

Summary

While it might be more common for new applications to use XML or JSON to transmit data the majority of legacy systems will still be using CSV for a very long time.

The lack of a standard can make working with CSV files tricky although that may be about to change in 2014.

The best you can do sometimes is insist on a consistent format for the CSV files received and point any strange setups to RFC 4180.

It is often quicker to have someone fix the format of the file sent to you than building a number of work arounds for poorly formatted files.

Further Reading:

Here are some articles that may be of interest:

• The Wikipedia entry on CSV is quite good
• How to import or export text (.txt or .csv) files from Microsoft Excel
• When and Why is XML preferable to CSV? answered on stackoverflow
• So You Want To Write Your Own CSV code? - a call for not re-inventing the wheel