A CSV is a comma separated values file, which allows data to be saved in a table structured format. CSVs look like a garden-variety spreadsheet but with a .csv extension (Traditionally they take the form of a text file containing information separated by commas, hence the name).



If you are experiencing issues with the upload of your file, it may be due to a CSV formatting error within the file.

 

Please see below for some useful information regarding CSVs.

 

CSV Format

  • Each record is one line - Line separator may be Carriage Return or Line Feed
  • Fields are separated with delimiter (commas or tabs are supported)
  • Leading and trailing white spaces are ignored - Unless the field is delimited with double-quotes in that case the white space 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

CSV Best Practice

A good CSV will look like this:

Organisation ID,Name,Email

1186,Fred,fred@domain.com

1187,George,george@domain.com


Resulting in a table like this:

 

Organisation ID Name Email

1186

Fred fred@domain.com

1187

George george@domain.com

 

The first row is a header row that contains the names for the columns (note that this row is not mandatory)

The remaining rows are data rows that always contain the same number of fields.

 

Line endings use CRLF (Windows line endings) and the column fields are separated by the delimiter (a comma).

If a field contains a comma, a line ending or quotes then the field should be wrapped by double quotes. For example: Springfield "Nuclear Power" Plant would need to be "Springfield ""Nuclear Power"" Plant"

OrganisationCode,OrganisationName,Address1,Address2,Address3,City,Postcode
0-23176921,"Springfield ""Nuclear Power"" Plant" ,Head Office,"HR House, 742 Evergreen Terrace",
Washington,Springfield,PO BOX


which would result in a table like this:

OrganisationCode

OrganisationName

Address1

Address2

Address3

City

Postcode

0-23176921

Springfield "Nuclear Power" Plant

Head Office

HR House, 742 Evergreen Terrace

Washington

Springfield

PO BOX


Common CSV Errors

  • There are encoding errors because of odd characters in the file (such as ć)
  • Line breaks are not consistent
  • Every row in the file must have the same number of columns
  • Inconsistent use of quotation marks
  • There shouldn't be any white spaces between commas and double quotes around fields

If you are struggling to find an error within your file, there are multiple free online tools that can help you pinpoint the error.