How to Format and Import Bank Statement CSV into Xero

Xero provides flexible bank statement import capabilities, but like all double-entry accounting engines, it requires cleanly formatted CSV files to ensure reconciliation integrity.

Xero requires at minimum two columns to import any statement:

• Date: The date the transaction cleared your account.

• Amount: The monetary value of the transaction.

To make reconciliation efficient, Xero also accepts four optional standard columns: Payee, Description, Reference, and Check Number. Supplying Payee and Reference allows Xero’s automated bank rules and machine learning matching algorithms to reconcile transactions automatically.

• File limit: Xero recommends files under 1,000 transaction rows (or under 2 MB) to prevent browser timeouts during bank indexing.

• Regional date formats: Xero relies on the short date format configured in your Organization Settings (DD/MM/YYYY for UK, Australia, New Zealand, and global editions; MM/DD/YYYY for US editions). You must verify that your CSV date column matches the date format selected during the Xero column assignment step.

Xero supports two distinct approaches for recording monetary values:

Approach A: Single Amount Column (Recommended)

• Deposits (money received) must be positive numbers.

• Withdrawals and payments (money spent) must be negative numbers with a leading minus sign (-).

Approach B: Two Columns (Spent and Received)

• Create one column named "Received" and another named "Spent".

• Both columns must contain positive numbers; do not put minus signs in the Spent column when using two separate columns.

Before saving your spreadsheet as a CSV for Xero:

1. Row 1 must contain clean header names. Remove bank marketing banners, customer account numbers, and opening balance summaries.

2. Verify that dates align with your Xero organization settings (DD/MM/YYYY for UK, Australia, and New Zealand; MM/DD/YYYY for US organizations; or YYYY-MM-DD universally).

3. Remove blank rows between transactions. Xero stops parsing when it encounters entirely blank rows.

4. Save the file as CSV (Comma delimited) (*.csv) with UTF-8 encoding.

To upload your prepared CSV into Xero:

1. In Xero, go to Accounting > Bank accounts.

2. Find the bank account you want to import into, click the Manage Account menu, and select "Import a statement".

3. Under Statement file, click Browse or drag your CSV file into the box.

4. Click Import.

5. On the Statement Import Options screen, verify your column mappings. Ensure the Date format dropdown matches your CSV dates.

6. Review the first few transaction rows displayed in the mapping preview. Verify that amounts appear in the correct Money In or Money Out sections.

7. Click Save to complete the import. Transactions will immediately appear on your Bank Reconciliation dashboard.

Issue: Transactions imported with inverted signs (expenses imported as income).

Solution: If money out appeared in the Received column, reopen your spreadsheet and ensure debits have a negative sign (-) in a single amount column, or re-map your columns during Step 5.

Issue: Accidental duplicate transaction imports.

Solution: Xero provides an automatic duplicate check. If duplicate rows slip through (such as overlapping date ranges between statement periods), go to Accounting > Bank accounts > select the account > Bank statements tab. Find the uploaded statement batch, click into it, select the duplicate lines, and click Delete.