A French CSV export that Excel mangled for two years

Our finance team in Lyon exports a monthly settlement CSV from an internal Django admin. Around 180k rows, 14 columns, semicolon-delimited because Excel on French Windows expects that. For about two years they opened the file, and dates showed as text, amounts with four decimals turned into scientific notation, and a column named Référence came out as Référence on one colleague’s machine but not another’s. Nobody filed a bug because everyone assumed it was Excel being Excel.

The export code was doing this:

response = HttpResponse(content_type="text/csv")
response["Content-Disposition"] = 'attachment; filename="reglements.csv"'
writer = csv.writer(response, delimiter=";")
writer.writerow(headers)
for row in rows:
    writer.writerow(row)
return response

Django’s HttpResponse defaults to charset=utf-8, but it does not emit a BOM. Excel on a French locale will happily guess the encoding and guess wrong depending on the machine’s regional settings. The date and amount problems were separate: we were writing Python str(datetime) and str(Decimal) straight through, so Excel had no type information at all and fell back to whatever its locale parser decided.

What I changed, in two parts. First, the encoding:

response = HttpResponse(content_type="text/csv; charset=utf-8-sig")
response.write("\ufeff")

Second, typed cells. Dates as JJ/MM/AAAA (the format the finance team actually reads), amounts as a plain decimal string with a comma separator and no thousands grouping, and any field starting with =, +, -, or @ prefixed with a single quote to stop formula injection. I also added a sep=; first line, which is ugly but makes Excel skip its own delimiter sniffing on French and German installs.

The BOM is the part worth explaining. utf-8-sig writes the three bytes EF BB BF before the first character. Excel treats that as an explicit encoding declaration and stops guessing. The cost: any downstream parser that reads the file as bytes and does not strip the BOM will see \ufeffRéférence as the first header. We hit exactly this — a small Node script a colleague wrote to reconcile the file started failing with a header lookup miss. We now strip it on read:

text = raw.decode("utf-8-sig")

Where this falls apart: if the recipient opens the file in LibreOffice with a non-UTF-8 default, the BOM is harmless but the sep=; line shows up as a real data row. Numbers with a comma decimal separator will be parsed as text by any English-locale tool that opens the file, which is fine for finance and painful for anyone scripting against it. We keep two export endpoints now — one for humans, one for machines — and the machine one is plain UTF-8, comma-delimited, ISO dates.

The thing I still do not know: whether there is a way to make a single CSV that both Excel-on-French-Windows and a pandas read_csv() default call handle identically, or whether everyone just accepts two exports. If you have solved that, I would like to see how.