One bank CSV, three importers: the format QuickBooks Online, Xero and Wave will all accept

#csv #python #accounting #quickbooks

The safest bank transaction CSV for QuickBooks Online, Xero and Wave has three columns, Date, Description and Amount, with one header row, one transaction per row, a single signed amount (money in positive, money out negative), no currency symbols, no thousands separators, and dates in one consistent format that matches the company's region. Each app accepts more than that, but all three accept that.

If you write converters, scripts or bookkeeping tools, it pays to know where each importer is strict, because the error messages rarely say what is actually wrong.

Where each importer differs

Based on each vendor's help pages as of November 2026. Importers change, so re-check the current help page before you rely on an edge case.

	QuickBooks Online	Xero	Wave
Amount layout	3 columns (one signed Amount) or 4 columns (separate Credit and Debit)	One signed Amount column	One Amount column, or separate deposit and withdrawal columns
Extra columns	Ignored if you don't map them	Payee, Reference, Cheque Number are optional named columns	You pick which column is which during upload
Date	One date column; the format must be consistent	Must match the organisation's regional format	Only one date column; two date columns cause errors
Things that trip it	Currency symbols, commas in numbers, blank rows	Column order and header names	Symbols other than the currency sign, descriptions that are only numbers

Wave's own help suggests that if a split deposit and withdrawal file won't load, move the amounts into one column. That's the main reason the single signed column is the common denominator.

A normalizer

Real statement extractions come in every shape: debit and credit columns, (45.00) for negatives, 45.00 CR, $1,204.18, dates like Oct 3 with the year only in the header. Normalize once, then write whichever file you need.

import csv
from datetime import datetime
from decimal import Decimal

def money(raw: str) -> Decimal | None:
    s = (raw or "").strip().upper().replace("$", "").replace(",", "")
    if not s:
        return None
    sign = -1 if (s.startswith("(") or s.endswith("-") or s.endswith("DR")) else 1
    s = s.strip("()-").replace("CR", "").replace("DR", "").strip()
    return sign * Decimal(s)

def normalize(rows, date_format: str, debit_col=None, credit_col=None, amount_col=None):
    out = []
    for r in rows:
        if amount_col:
            amount = money(r[amount_col])
        else:
            debit = money(r.get(debit_col, "")) or Decimal("0")
            credit = money(r.get(credit_col, "")) or Decimal("0")
            amount = credit - abs(debit)
        if amount is None or amount == 0:
            continue
        d = datetime.strptime(r["date"].strip(), date_format).date()
        desc = " ".join(r["description"].split())       # collapse wrapped lines
        if desc.isdigit():
            desc = f"Ref {desc}"                         # Wave rejects number-only descriptions
        out.append({"Date": d, "Description": desc[:200], "Amount": amount})
    return out

def write_csv(path, txns, us_dates=True):
    fmt = "%m/%d/%Y" if us_dates else "%d/%m/%Y"
    with open(path, "w", newline="") as f:
        w = csv.writer(f)
        w.writerow(["Date", "Description", "Amount"])
        for t in txns:
            w.writerow([t["Date"].strftime(fmt), t["Description"], f'{t["Amount"]:.2f}'])

f'{amount:.2f}' on a Decimal gives -1204.18: no comma, no symbol, always two decimals. That one line removes the most common import failure.

Card statements need a sign flip

On a credit card statement, a purchase increases what you owe. Some extractors write it as positive. Accounting importers expect a card purchase to come in as money out, the same as a debit from checking. Flip the sign for card accounts before writing, and flip payments to the card the other way. Do it by account type, not by guessing from the description.

Check before you import, not after

An importer will happily accept a file that is missing half a page of transactions. It can't know. You can:

def ties(txns, opening: Decimal, closing: Decimal) -> bool:
    return opening + sum(t["Amount"] for t in txns) == closing

If that returns False, don't import. Find the gap first. Cleaning up a bad import in QuickBooks or Xero (undoing matches, deleting duplicates) takes much longer than fixing the CSV.

What about .qbo, OFX and IIF?

Some importers prefer OFX or QBO files when the bank provides them, and it's worth using those if your bank exports them directly. If you are starting from a PDF, CSV is the honest choice: it's what you can build and verify yourself, and all three apps accept it.

The bookkeeper version of this problem

For one client, a script is fine. A bookkeeper with 30 clients is running this every month for every account, which is where the time goes. Disclosure: I work on ZeraBooks. The ZeraBooks Converter writes QuickBooks-ready and Xero-ready CSV from PDF statements, checked against the statement balances first. For bookkeepers who would rather skip the import step entirely, there is a write-up on bookkeeping automation for bookkeepers that covers keeping every client's books in one place.

ZeraBooks Convert's download step: pick the importer's layout (QuickBooks-ready, Xero-ready, Sage-ready) and the file type, after the statement is proven.

DEV Community