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

To create a transaction CSV that Google Sheets imports correctly, you need to follow four key rules: quote fields according to RFC 4180, write amounts as plain numbers with two decimal places and no thousands separator, use a single documented date format, and neutralize text cells that begin with a formula character. If any of these rules are not followed, Google Sheets will silently reinterpret the file.

For amounts, write them as plain numbers with two decimal places and no thousands separator. For instance, use 1234.56 instead of 1,234.56. This is crucial because amounts are typically written with a minus sign for money out; parentheses are not used for negatives in a CSV file as Sheets may misinterpret them as text.

Dates in Google Sheets are tricky. The correct format is 2026-03-02, which is the least ambiguous across all locales. However, many accounting imports expect MM/DD/YYYY. If you choose to use this format, make sure to document it clearly in your CSV and instruct users to adjust their locale settings before importing. A common mistake with dates is that different locales interpret 03/02/2026 differently; for example, it could be March 2 in the U.S. or 3 February in the U.K.

When quoting a bank CSV, rely on the CSV library to handle quotations. The Python csv module can be used to write rows safely, with QUOTE_MINIMAL, which adds quotes only when necessary and handles inner quotes by doubling them. This prevents issues with commas within bank descriptions or payees.

To stop CSV formula injection, which can cause problems when a user attempts to import data with formulas, prefix any text that starts with a formula character (like "=" or "+") with an apostrophe. This tells Google Sheets to treat the cell as text rather than a formula. Always treat bank descriptions as user input, as they are often written by the payer and may contain unwanted characters.

A clean sample CSV should have the following format: Date,Description,Amount. For example:

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

After importing, users can verify that the import was complete by checking if the closing balance equals the sum of the opening balance, the sum of all amounts (deposits are positive, withdrawals are negative), and minus the closing balance. If the result is not zero, it indicates that something did not import correctly, such as a missing deposit or an amount written as text instead of a number.