How to make a Xero bank statement CSV from a PDF

Xero can import a bank statement from a CSV file when a live bank feed is not available. This is how to build that file from a PDF statement.

Start from Xero's own template

Xero publishes a sample CSV for statement imports. Download the current one from the import screen and match it, rather than relying on memory or on an older guide. The columns have included Date and Amount (required), with optional fields such as Payee, Description, Reference and Check Number. Treat the template you download as the authority, since it can change.

Why bother with a CSV at all

A bank feed is the easiest route when your bank supports it. A CSV import is useful for older periods before a feed was connected, for accounts with no feed, or for statements a client sends you as PDFs. Because an import adds rows to the account, getting the file right the first time saves you from deleting and re-importing later, and the balance check below is the cheapest way to know that.

Steps

Download a text-based PDF statement from your bank.
Convert it to CSV, giving one row per transaction.
Match the template. Open Xero's sample and the converted file side by side. Rename headers to match, keep a single header row, and delete columns Xero does not use, such as a running balance, or map them away.
Use one signed Amount column. Money received is positive and money spent is negative. If your CSV has separate debit and credit columns, combine them as credit minus debit.
Fix the dates. Pick one format and use it throughout. In the import screen, confirm how Xero reads it, particularly when the day could be mistaken for the month.
Prove the file. Opening balance plus the sum of the Amount column should equal the closing balance on the statement. Fix any gap before importing.
Import and review. Upload through the bank account's import option, check the preview, and then work through reconciliation.

Common snags

Currency symbols or thousands separators in the Amount column.
Extra lines above the header, such as the account name.
Overlapping date ranges that bring in transactions you already have.
Long descriptions with commas that are not quoted.

The guide to import errors goes through these in more detail.

Where PennyTie fits

PennyTie can do the first and last steps: it converts a text-based statement PDF to CSV in your browser, and checks that opening balance plus transactions equals the closing balance to the cent, flagging rows to look at. Nothing leaves your device.

Limits

PennyTie reads text PDFs only (no OCR). It does not write a file in Xero's exact layout automatically, so expect to rename a column or two. Always review flagged rows against the original statement before you import.

Try it: open PennyTie, drop a text-based statement PDF (or use the built-in fictional sample) and see whether the totals tie out. Free for statements up to 5 pages; nothing is uploaded.