CSV and Delimiters

A CSV file is plain text arranged as a table: one record per row, fields separated by a delimiter. Each record usually occupies one line, but a record is not always a physical line. Commas are typical; tabs, semicolons, and pipes are also used in delimited files. Check the delimiter in the file rather than assuming it from the filename.

The producing program’s settings can change the delimiter. Microsoft documents that setting Excel’s decimal separator to a comma makes Excel use a semicolon when saving .csv. That behavior depends on the program and its settings; it is not a rule for every CSV writer.

RFC 4180 (section 2, item 6) says that fields containing line breaks, double quotes, or commas should be enclosed in double quotes. A quoted field can therefore contain a line break, and splitting the file on line breaks can cut one record into two. In the example below, the file has three physical lines but only two records: the header and one data row whose note field spans two lines.

id,note
1,"first line
second line"

Because the file is plain text, it stores no column types, no formula definitions, no formatting, and no column widths; every value is text. That is its virtue as an exchange format and the source of every import decision you have to make. Storing no formulas does not stop a spreadsheet from interpreting a text value as one: OWASP notes that spreadsheet programs such as Excel and LibreOffice Calc interpret cells starting with an equals sign as formulas when they open a CSV. Delimiters also collide with data: a field containing a comma is wrapped in quotation marks, and a quotation mark inside such a field is doubled. When those rules are applied inconsistently by whatever produced the file, rows arrive with the wrong number of columns.

At import, the application asks which delimiter applies — Microsoft’s instruction is to “choose a character to use from the list under Delimiter” — and then guesses a type for each column. Two documented consequences deserve a check every time: leading zeros vanish when an identifier is guessed to be a number, and anything resembling a date is converted to one. Microsoft’s own guidance concedes that “if Excel does not convert a column of data to the format that you want, you can convert the data after you import it”; setting the column to text during the import is the version that does not lose data first.

Two more properties decide whether the file is readable at all. Nothing in the format states whether the first line is a header or the first record, so you tell the import which it is. And nothing inside the bytes reliably states the character encoding, which is why names arrive as mojibake when the import assumes the wrong one — an encoding mismatch to fix at the import step, not a typo to search and replace.

References

• Import or export text (.txt or .csv) files
• RFC 4180: Common Format for CSV Files
• OWASP: CSV Injection