How to Import a Bank Statement Into Xero

When a client’s Xero bank feed is missing or starts too late, import their statement by hand. Prepare the CSV, map columns, then check and export lines.

Published
Updated

To import a bank statement into Xero, open the client’s bank account, choose Import a Statement and upload a file covering the missing transactions.

Before you confirm the import, check that the dates and amounts read correctly. Afterwards, compare the statement balance at the end of the period with the client’s bank statement.

When to Import Instead of Using a Bank Feed

Import manually when Xero has no feed for the client’s bank account, the client hasn’t authorised the feed or the feed starts after the period you need.

On Xero business plans, Advisors and Standard users with bank account access can import statements. On Xero partner plans, Advisors and both Cashbook client roles can import them. See Xero’s partner plan permissions.

Open Bank accounts from Accounting, then select Bank Statements from the account’s three-dot menu. In Statement lines, look for the earliest transaction with Bank Feed in the Source column.

Use the date of that transaction as the first evidence of feed coverage. The import date tells you when Xero received it. Compare the existing lines with the original statement to find the gap.

Keep only missing transactions in your import file. If you include transactions already in Xero, duplicate lines can change the statement balance.

Statements Collected From Clients

Ask for one internet banking export per account covering the exact dates you’re working on. Request OFX if available, or ask for CSV or QIF.

Xero also accepts QFX and QuickBooks Online bank statement files. See Xero’s supported import formats.

If the client only has a PDF, open it in the bank statement converter. It reads supported text PDFs and scans. Check each row’s date, amount and direction against the PDF.

Add the net movements to the opening balance. Check the result against the closing balance before downloading the CSV.

Keep the original bank statement with the client’s records. Ask for a fresh export if the converter cannot read the layout or the rows still don’t balance after review.

Once you have checked CSV rows, you can also convert CSV to QIF.

Prepare the Statement File

Download the CSV template from Xero’s Import a Statement screen. The template uses headings for your browser’s language. Copy in the transaction rows and keep the headings in the first row.

Date and Amount are required. Include Payee, Description and Reference if the bank supplies them. Description appears under Particulars in Xero’s Bank Statements view. The template also has a field for the cheque number where relevant.

Use one date format throughout, such as DD/MM/YYYY. Put money in and money out in the same Amount column. For example, record a $120 receipt as 120.00 and a $30 payment as -30.00.

If you used the converter, rewrite its YYYY-MM-DD dates as DD/MM/YYYY. For each row, subtract Debit from Credit and put the result in the template’s Amount column. Copy Description across.

Remove opening and closing balance rows, summary totals and blank rows. Drop any balance columns and the bank account number.

Xero calculates the statement balance from the statement lines, so a total entered as a transaction would change the balance.

Import the Statement Step by Step

  1. In Accounting, open Bank accounts and find the client’s matching account.
  2. Open that account’s three-dot menu and choose Import a Statement.
  3. Under File to upload, choose Select file or drop the file into the upload area. Click Next.
  4. For CSV, map Date to Transaction Date and Amount to Transaction Amount if Xero prompts you. Match the other columns you need and click Next.
  5. If Xero asks for a date format, select the one used in the file and click Next. This can also happen with a QIF file.
  6. On Review, check a receipt and a payment against the original statement. Clear the checkbox beside any line you intend to leave out. Check how many lines you expect to import.
  7. Click Complete import. Open the new statement under Bank Statements and check the imported line count. Check the account’s statement balance.

Xero reuses the saved mapping for matching CSV headings. If the preview looks wrong, open Import settings from Review and correct the mapping before confirming. See Xero’s CSV import instructions.

Check the Imported Lines

Compare Xero’s calculated statement balance with the client’s closing balance on the same date. If Xero already has later transactions, run the Bank Reconciliation Summary with the statement’s closing date as the report end date.

Compare the dated lines as well. Check the first and last transactions, then look for missing lines or repeats where the import meets the feed. A matching balance can hide a missing receipt and payment of the same amount.

Xero skips CSV lines it matches to existing transactions at import. The Statement Exceptions report can show duplicates picked up at that stage. Use the Duplicate Statement Lines report to check for possible duplicates still in the account.

Xero checks for matching dates and amounts, then looks for an exact match in the reference, particulars or payee. Compare each pair with the bank statement before deciding. See how Xero flags duplicate statement lines.

For a confirmed duplicate, keep the reconciled copy if it matches the bank’s details. Two separate payments for the same amount on the original statement need two lines in Xero.

Export Statement Lines From Xero

To export the lines for a reviewer or the tax return workpapers, open All reports from Reporting. Open the Bank Reconciliation report pack and choose Bank Statement under Contents.

Set the Pack date range and Bank Account. Under Statement lines to include, select All statement lines to include unreconciled lines.

Click Update, then Export. Choose a spreadsheet for line-by-line checks or PDF for a fixed copy.

The Bank Statement report lists the individual statement lines. The Bank Reconciliation Summary compares the bank statement balance with the balance recorded in Xero and shows items still outstanding or unreconciled. See Xero’s Bank Reconciliation report pack.

When the Import Fails

Use the preview and the original statement to work out whether the problem is in the file or the import settings.

What you seeWhat to checkWhat to change
Xero rejects dates or puts them in the wrong monthLook for mixed date formats, extra spaces or a saved setting that reverses the day and month.Use one date format such as DD/MM/YYYY. From Review, open Import settings and click Next. If Xero prompts you, select the format used in the file.
Amounts land in the wrong field or payments appear as receiptsCheck which column maps to Transaction Amount. Look for separate money-in and money-out columns or reversed signs.Combine the amounts into one column with receipts positive and payments negative. Map that column to Transaction Amount.
The CSV is too large or the upload times outCount the rows. Xero’s CSV import limit is 100,000 rows, and an upload can time out below that limit.Remove blank rows. If it still fails, split the file into smaller batches with no overlapping lines, or export OFX from the bank.
Xero skips lines as duplicatesCompare the skipped lines with existing lines in Bank Statements and the original statement.Leave the existing copy in place if it matches. If two bank transactions really occurred, check which one is missing before retrying.

After correcting the file, compare the new preview against the bank statement before you confirm. Once the statement lines and closing balance agree, see the guide to bank reconciliation in Xero for the next step.

One client form. One finished return.

Your client completes one guided form. Tax Genie checks it, chases mistakes and sends the details to your engagement letter and tax software.

See how Tax Genie works

Made for Australian accounting firms

Tax GenieSee how Tax Genie works