Bank statement to Xero
A CSV Xero can import, with the balance column kept — Xero maps columns itself, so it does not misread the extra one.
Columns: Date, Description, Amount, Balance
No statement handy? or open the finished Excel
What goes wrong
Overlapping periods, not overlapping columns
Xero asks you to map each column yourself, so an extra column is harmless — the opposite of QuickBooks. What catches people here is the date range. Xero tracks which statement lines it already holds for an account, and importing a file that overlaps a period already imported produces duplicate statement lines that then have to be removed by hand, one reconciliation at a time. Convert and import one statement period at a time, and check the first and last date of each file before uploading.
Getting the file into Xero
- 1Convert the statement above and press CSV.
- 2In Xero, open the bank account you are importing into and choose to import a statement.
- 3Map the columns when asked: date, description and amount are the three Xero needs; the balance column can be left unmapped.
- 4Tell Xero the date format is yyyy-mm-dd.
- 5Check the date range it reports against what is already in the account before you confirm.
Xero questions
Should I map the balance column?+
No need. Xero derives the running balance itself from the transactions and the account's opening figure. Leave the column unmapped and keep it for your own checking.
Can I use the QuickBooks file instead?+
You can — it is a valid CSV and Xero will map its three columns happily. You would just lose the balance column that lets you check the import against the statement.
What if Xero rejects the file?+
The usual cause is a date format it read differently from what you selected, which shows up as transactions landing in the wrong month. Re-import with the format set explicitly to yyyy-mm-dd rather than letting it guess.