Two Things Every CSV Export of Untrusted Text Needs Before Someone Double-Clicks It in Excel

I build a browser tool that reads email archives and can export a mailbox as a CSV index: one row per message with date, sender name, sender address, subject and an attachments flag. People use it to get a mailbox "into Excel".

That export shipped with two bugs that never showed up in a text editor. Both appear only when someone double-clicks the file and Excel opens it. Here they are and how I fixed them.

Problem 1: Excel doesn't assume UTF-8

My CSV was valid UTF-8. Opened in VS Code, Grüße and 日本語 looked fine. Double-clicked in Excel, they came out as mojibake.

The reason: when Excel opens a CSV directly, it decodes it in the system's ANSI code page unless the file starts with a UTF-8 byte-order mark. Subjects and sender names in real mail are full of accented and non-Latin characters, so this hits almost every real mailbox.

The fix is three bytes, EF BB BF, at the very start of the file. I put it in one function that every CSV download goes through:

export const CSV_UTF8_BOM = "\ufeff";

/** The bytes of a CSV download — every CSV this app writes goes through here. */
export function csvBytes(csv: string): Uint8Array {
  return new TextEncoder().encode(CSV_UTF8_BOM + csv);
}

TextEncoder writes U+FEFF as the three-byte UTF-8 sequence, so the file starts with exactly what Excel looks for.

The important part is the single choke point. There were three CSV downloads: one folder, the whole archive, and search results. The search-results export was built in the UI rather than in the worker. One of them going out without the BOM was the real risk. The test reads the source files and asserts that all three call sites use csvBytes(), and that no plain encoder.encode(csv) remains.

Problem 2: other people's text can be a formula

The cells in this CSV aren't mine. They're email subjects, sender names and folder or label names, all typed by whoever sent or organised the mail. A subject line like this:

=HYPERLINK("http://example.com/x","Invoice")

was written into the CSV verbatim. Spreadsheet apps (Excel, LibreOffice, Google Sheets) read a cell that starts with = as a formula. That's CSV injection (also called formula injection). OWASP has a page on it, and the classic proof-of-concept payload is something like -2+3+cmd|' /C calc'!A0.

The trigger isn't only =. OWASP's list of leading characters is:

• =
• +
• -
• @
• tab (\t)
• carriage return (\r)

The recommended mitigation is to prefix such a value with a single quote ('), which spreadsheets treat as "this is text".

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

export function csvCell(value: string): string {
  const text = FORMULA_START.test(value) ? `'${value}` : value;
  return /[",\r\n]/.test(text) ? `"${text.replace(/"/g, '""')}"` : text;
}

Order matters: guard first, quote second. The apostrophe has to end up inside the quoted field, so a value that both starts with = and contains a comma becomes "'=…, …". If you quote first and then prepend, the apostrophe ends up outside the quotes and the field is malformed.

The quoting itself is plain RFC 4180: wrap the field in double quotes if it contains a comma, a double quote or a line break, and double any embedded quotes.

The cost, stated plainly

This guard isn't free. An honest subject like -- weekly report or +1 for Friday now shows a leading apostrophe in some views. That's the trade OWASP recommends, and I'd rather have a stray apostrophe than a formula that runs. But users will see it, so the page that describes the export says so, instead of letting someone discover it and think the export is broken.

Make it impossible to bypass

Before this change, the code had two private escaper functions, each with its own idea of what to quote. That's how one path ends up guarded and another doesn't. They're gone. Every text cell in all three export paths now goes through csvCell(), and a test checks that:

it("neutralises every formula trigger", () => {
  expect(csvCell("=1+1")).toBe("'=1+1");
  expect(csvCell("+1 Sales")).toBe("'+1 Sales");
  expect(csvCell("-2+3+cmd|' /C calc'!A0")).toBe("'-2+3+cmd|' /C calc'!A0");
  expect(csvCell("@SUM(A1:A9)")).toBe("'@SUM(A1:A9)");
  expect(csvCell("\t=1")).toBe("'\t=1");
  expect(csvCell("\r=1")).toBe(`"'\r=1"`);
  expect(csvCell('=HYPERLINK("http://example.com/x","Invoice")'))
    .toBe(`"'=HYPERLINK(""http://example.com/x"",""Invoice"")"`);
});

Other tests cover values that should be left alone, run a crafted mailbox through both exporters, and grep the exporter and the UI to make sure every text field goes through csvCell and no private escaper is back.

Limits

• The apostrophe prefix is a mitigation for spreadsheet apps, not a general sanitiser. If your CSV is consumed by another program that does its own interpretation, check what that program treats as special.
• The BOM is what Excel needs on a double-click. Some strict CSV parsers treat it as part of the first header name, so if your CSV also feeds a pipeline, make sure that consumer strips it.
• Dates are written as ISO 8601 in UTC, which sorts correctly as text. Excel may or may not convert them to its own date type depending on locale. That's a separate problem I didn't try to solve here.

Checklist

• Prepend a UTF-8 BOM to any CSV a person will double-click.
• Prefix values starting with = + - @ \t \r with ', then apply RFC 4180 quoting, in that order.
• Route every CSV path through one encoder and one cell function, and test that nothing bypasses them.
• Tell users about the visible apostrophe.

If you want to see what the export looks like on a real mailbox, the MBOX viewer page explains the CSV index and what goes into each column. Disclosure: I built it.