Building a CSV converter that survives contact with Excel

Step 1: three bytes that decide whether names are readable

A UTF-8 CSV with no byte order mark opens in Excel on Windows as the system ANSI code page. Every accented character becomes mojibake: café arrives as café. This is the single most common complaint about every CSV export ever written, and it is fixed by three bytes at the front of the file.

It is on by default here, with an option to turn it off, because a small number of downstream systems reject the mark. Defaulting to the option that works in the tool most people will open the file with is the whole job.

Step 2: stop Excel rewriting the data

Excel does not just display what it imports, it interprets it. A postcode of 01234 becomes the number 1234. A sixteen-digit order number becomes 1.23457E+15, and the original digits are gone rather than merely hidden. A product code of SEPT1 becomes a date. There is a well-known paper about this destroying gene names in published research.

In a CSV there is nothing you can do about it, because a CSV has no type information: every value is text and Excel guesses. In an XLSX the type of every cell is recorded, so a value written as text stays text. That is the real argument for converting to a genuine workbook rather than renaming a CSV.

The rule for what to pin is deliberately narrow. Marking every cell as text would stop real numbers being numbers and break every formula downstream, so only values that Excel would actually change are pinned, and the count is reported.

// Values Excel would rewrite, and only those:
//   leading zeros      "01234"  -> 1234
//   long digit strings  16+ digits -> 1.23457E+15
//   date-like codes     "SEPT1" -> 1 September
// Everything else keeps its natural type, or every formula
// downstream breaks.
function needsTextPin(value: string): boolean {
  if (/^0\d+$/.test(value)) return true;
  if (/^\d{16,}$/.test(value)) return true;
  if (looksLikeAccidentalDate(value)) return true;
  return false;
}

Step 3: detect the delimiter by consistency, not by counting

The obvious way to detect a delimiter is to count candidates and pick the most frequent. It fails on the first file containing prose, because a column of sentences has more commas in it than a semicolon-separated file has semicolons.

The signal that actually works is consistency: the right delimiter splits every line into the same number of fields. A wrong one produces a ragged row count. Scoring on that is barely more code and is right on the files where counting is wrong.

Step 4: report the cells that are executable

A cell beginning with `=`, `+` or `@` is a formula to a spreadsheet, not text. A workbook built from data somebody else supplied is therefore a way of running their content on the machine that opens it. This is a real and well-documented class of attack.

The tool counts those cells and tells you, and does not alter them. Silently rewriting somebody’s data to make it safe is its own kind of wrong: if a column legitimately contains formulas, mangling them without asking is a bug rather than a feature. Reporting is the honest position, and it puts the decision with the person who knows where the file came from.

What went wrong

The most useful part of any build write-up, and the part usually left out.

• Converting only the first sheet of a workbook throws away the rest without saying so. A workbook with a sheet per month is the normal case, not the exception.
• Naming the CSV `.xlsx` does not make it a workbook, and does not stop Excel guessing types. The conversion has to be real for the type pinning to mean anything.
• Reading the file through a Blob makes the core untestable, because jsdom has no `Blob.arrayBuffer`. Taking bytes instead means the whole parser is testable in Node.
• Excel in many European locales expects a semicolon, because the comma is the decimal separator there. A comma-separated file opens as one column, and the user has no idea why.