How to import bank statements into Xero.

The template columns.

Xero's US template has six columns: Date, Amount, Payee, Description, Reference, and Check Number. Date and Amount are required. Amount is one column for both money in and money out: Income positive, expenses negative. Payee is matched to a contact when it equals an existing contact name exactly. Reference and Check Number are optional and have length limits.

The rules Xero publishes.

1. One date format throughout. Xero accepts several, and when a date could be read two ways it asks you to confirm the format. The choice then applies to every future CSV import for that account, so choose carefully. MM/DD/YYYY is the safe choice for a US file.

2. No currency symbols. No commas as decimal separators.

3. No balance column, no account number column, and no opening or closing balance rows. Xero computes the balance from the transactions.

4. A reference of at most 255 characters and a check number of at most 20.

5. No zero-amount lines; they are refused.

6. Split files above 1,000 transactions. Xero's limit is higher, but its own troubleshooting article says large files time out above that.

7. Credit card accounts: Purchases negative, payments to the card positive.

8. Transactions that match ones already in Xero are treated as duplicates and skipped.

Getting the rows out of the PDFs.

If the statements are PDFs, the rows have to be read out of them and checked first. This is the step Ledgerfoot does. Each statement is read into rows and checked against its own printed balances. The Xero bank statement file is built in the template's columns with every rule above applied and validated before download. A statement that does not foot is flagged, and its rows carry the flag into the file.

Importing, step by step.

1. In Xero, open Accounting, then Bank accounts.

2. On the account, open the menu and choose Import a Statement.

3. Drag the CSV in or select it, and go to the next step.

4. If asked, assign the columns to Xero's fields. The mapping is remembered.

5. If asked, choose the date format.

6. Review the lines, clear any you do not want, and complete the import.

7. Reconcile the imported statement lines against invoices, bills, and spend or receive money transactions, month by month.

OFX and the others.

Xero also accepts OFX, QFX, and QIF files, and PDF statements from banks it supports. Xero recommends OFX where the bank offers it, because the file needs no editing. For a bank that offers only PDFs, the CSV route above is the one that works for every bank.