Simple


A .csv file stands for comma separated values file. This allows data to be saved in a table structured format. CSVs can look just like a standard spreadsheet but use the .csv extension. CSV files can be used with any spreadsheet program, e.g. Microsoft Excel, Open Office Calc, or Google Spreadsheets. CSVs differ from other spreadsheet file types because you can only have a single sheet in a file, you can not save: formulas or cell, column, row formatting.


Make sure your file is separated with commas ',', semicolons ';' and tabs. Other separators, such as or pipeline '|', will cause problems.


Examples


Correct

 

"Name","Company","Email Address"
"Dave","Buttons Inc","[email protected]"
"Mike","Zips Co","[email protected]"

 

Incorrect


Incorrectly separated with pipelines (|)


"Name"|"Company"|"Email Address"
"Dave"|"Buttons Inc"|"[email protected]"
"Mike"|"Zips Co"|"[email protected]"


Some Common Errors to check before uploading a CSV file


  • Wrong_content_type -- content type is not text/csv
  • Agged_rows -- row has a different number of columns (than the first row in the file)
  • Blank_rows -- completely empty row, e.g. blank line or a line where all column values are empty
  • Invalid_encoding -- encoding error when parsing row, e.g. because of invalid characters
  • Quoting -- problem with quoting, e.g. missing or stray quote, unclosed quoted field
  • Whitespace -- a quoted column has leading or trailing whitespace



In Depth

The RFC 4180 documents the format of comma separated values.


While there are various specifications and implementations for the CSV format (for ex. [4], [>5], [6] and [7]), there is no formal specification in existence, which allows for a wide variety of interpretations of CSV files.


Section [2] documents the format that seems to be followed by most implementations:


1. Each record is located on a separate line, delimited by a line break (CRLF) CR = Carriage Return and LF = Line Feed.


For example:


aaa,bbb,ccc CRLF

zzz,yyy,xxx CRLF


2. last record in the file may or may not have an ending line break.


For example:

aaa,bbb,ccc CRLF

zzz,yyy,xxx


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


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


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


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.


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


There are many free CSV checkers available on-line. Just search Google.