There are some format conventions that must be followed in order for csv files to be interpreted correctly. This site also explains proper csv formatting in detail, but there are a few important concepts and common issues that come up.

CSV stands for 'comma-separated values'.  The file might have ".csv" as the file extension, but it can actually be any plain text format, such as ".txt". The file should usually contain 1 header row that lists the column names, and as many data rows as necessary. Each column header and each piece of data is separated by the separator (also known as a delimiter). Each row must have the same number of columns, including the header row.

Name,Email
Ashley Jones,ashley@example.com
Tom James,tjames@example.com

However, the data doesn't necessarily have to use a comma as the separator. Other characters such as pipe (or "vertical bar", |) or tabs are also common, and can often be preferable to commas, since commas are often used in the data itself, while the pipe character is much rarer.

Structure|Width
Deck, front|10 ft
Deck, rear|15 ft

When using a character delimited format, if the raw data itself can contain the delimiter, a text qualifier must be used. Double quotes (") are the most common choice. What this means is that each value must be enclosed in text qualifiers, so that occurrences of the delimiter in the data are not interpreted as new columns.  In the following example, the delimiter is the comma character, and the text qualifier is the double quote character.

Structure,Width
Deck, front,10 ft
(incorrect, Deck and front will be treated as 2 separate columns, and 10 ft would be treated as an unknown, invalid 3rd column)

Structure,Width
"Deck, front",10 ft
(correct)

However, if the raw data can also contain the text qualifier (which is fairly common in permit data, since the " character is also used to denote inches), that character must be repeated twice in order for the import program to ignore it and not treat it as a text qualifier. Work description information often has this problem, but this issue can potentially occur in any text field in any csv file. 

Adding a 10'6" x 7' deck, in-ground pool, and shed
(incorrect, commas will be treated as new columns)

"Adding a 10'6" x 7' deck, in-ground pool, and shed"
(incorrect, the " after the 6 will be treated as the end of the data)

"Adding a 10'6"" x 7' deck, in-ground pool, and shed"
(correct)

If the data can contain the text qualifier (as in the above example), it is often simpler to use a different separator and/or text qualifier. The text above would not require any special handling if the separator was just the pipe (|) character.


Did this answer your question?