Writing a transaction CSV that Google Sheets imports correctly: locale, decimals and formula injection

To write a transaction CSV that Google Sheets imports cleanly, follow four rules: quote per RFC 4180, write amounts as plain numbers with two decimals and no thousands separator, use one documented date format, and neutralise text cells that start with a formula character. Break any of them and Sheets silently reinterprets the file.

If your app exports bank transactions, a good share of users will open the file in Google Sheets. Here is each rule with the failure behind it, and the code.

How should amounts be written in a CSV for Google Sheets?

"1,234.56" is a valid quoted field, but Sheets in a locale that uses a comma as the decimal mark will mangle it, and even in a U.S. locale you are relying on the parser to strip the comma. Write 1234.56.

from decimal import Decimal, ROUND_HALF_UP

def fmt_amount(x: Decimal) -> str:
    return str(x.quantize(Decimal("0.01"), rounding=ROUND_HALF_UP))

Money out is a minus. Don't use parentheses for negatives in a CSV; Sheets may read (250.00) as text.

Which date format does Google Sheets read correctly?

None, for every locale. 03/02/2026 is March 2 in a U.S. locale and 3 February in a U.K. one. 2026-03-02 is the least ambiguous, but many accounting imports expect MM/DD/YYYY.

Pick one, say so in your docs and UI, and tell users to set File, Settings, Locale before importing. The failure is silent:

Spreadsheet locale	03/02/2026 becomes	03/14/2026 becomes	-1234.56 becomes
United States	March 2, 2026	March 14, 2026	-1234.56
United Kingdom	3 February 2026	text, left-aligned	-1234.56
Germany (comma decimal)	not March 2	not March 14	text, or 123456

Days 1 to 12 flip month and day; days 13 and up stay as text. A sort by date then looks almost right, which is the worst kind of wrong.

How do you quote a bank CSV safely?

Let the library do it.

import csv

def write_rows(path, rows):
    with open(path, "w", newline="", encoding="utf-8") as f:
        w = csv.writer(f, quoting=csv.QUOTE_MINIMAL, lineterminator="\r\n")
        w.writerow(["Date", "Description", "Amount"])
        for r in rows:
            w.writerow([r.date.strftime("%m/%d/%Y"),
                        safe_text(r.description),
                        fmt_amount(r.amount)])

Payees contain commas (INSURANCE PREMIUM, POLICY 8812) and occasionally quotes. QUOTE_MINIMAL quotes those fields and doubles inner quotes. Don't hand-roll this.

What is CSV formula injection, and how do you stop it?

When a user imports with "Convert text to numbers, dates, and formulas" ticked, which they should for the amounts, a description that begins with = or + is treated as a formula. Bank descriptions are written by whoever sent the payment, so that is text you don't control.

FORMULA_START = ("=", "+")

def safe_text(s: str) -> str:
    return "'" + s if s.startswith(FORMULA_START) else s

A bank description is written by whoever sent the money. Treat it like user input, because it is.

A leading apostrophe makes Sheets keep the cell as text. OWASP's guidance on CSV injection lists more leading characters, including - and @. Decide your own list knowingly; - also starts every negative number, so apply it to text columns only.

What does a clean sample file look like?

Date,Description,Amount
03/02/2026,DEPOSIT CLIENT INV 1042,4000.00
03/03/2026,RENT MARCH,-2400.00
03/31/2026,"INSURANCE PREMIUM, POLICY 8812",-174.95

A converted statement in ZeraBooks Convert, one row per transaction, before it is written out as CSV.

How can users check the import is complete?

The most useful thing an export can include is not in the CSV. It is the opening and closing balance for the period, shown next to the download, so the user can run this in Sheets and get 0 when every row arrived:

=ROUND(opening + SUM(C2:C) - closing, 2)

For example, an opening balance of 8,412.90 with +6,250.00 in and -5,987.35 out must close at 8,675.55. A result of -48.20 means a 48.20 deposit is missing or came in as text.

The user-side walkthrough, including every Sheets import setting and the check formulas, is bank statement to Google Sheets.

Key takeaways

• Amounts: 1234.56, minus for money out, no thousands separator, no parentheses.
• Dates: one format, documented, with a note to set the Sheets locale first.
• Quoting: csv.writer with QUOTE_MINIMAL, never hand-rolled.
• Prefix text cells that start with = or + with an apostrophe.

FAQ

Can Google Sheets import a PDF?

No. File, Import accepts CSV, TSV, text and spreadsheet files. A PDF statement has to be converted first.

Should a CSV use ISO dates or MM/DD/YYYY?

ISO (2026-03-02) is the least ambiguous. Many accounting tools expect MM/DD/YYYY. Either works if you document it and users set the locale before import.

Why does SUM of the Amount column return 0 after import?

The "Convert text to numbers, dates, and formulas" box was unticked, so every amount is text. Import again with it ticked.

Disclosure: I'm the founder of ZeraBooks. These are the rules the ZeraBooks bank statement converter follows for its CSV: RFC 4180, amounts with a period and two decimals and no thousands separator, U.S. dates, and an apostrophe on descriptions that start with = or +. Each statement is checked against its printed balances before download. There is no Google Sheets connection; the file is the bridge.