How to convert a bank statement PDF into a CSV Xero will accept

Xero's bank feeds cover most accounts, but there are always gaps: a feed that dropped for three weeks, an account at a bank Xero does not connect to, or a new client whose last twelve months exist only as PDF statements. For those, Xero offers a manual statement import from CSV.

The import is quick. The difficult part is producing a CSV Xero accepts first time, with the money going in the right direction. A file with the signs flipped imports without any error and turns every payment into a deposit, and you only find out when the reconciliation will not balance.

What Xero expects

Xero's statement import maps a small set of columns:

• Date (required)
• Amount (required): one signed column. Money out is negative, money in is positive.
• Payee
• Description
• Reference
• Cheque number

Xero matches columns by name, so it helps to use those exact headers. The rules that cause most failed imports:

• Dates must match your organisation's regional format. A UK or Australian organisation will expect day-first dates; a US one, month-first. Use four-digit years and keep one format for the whole file.
• One signed amount column. If your data has separate debit and credit columns, merge them.
• Plain numbers. No currency symbols, no thousands separators, a full stop as the decimal point.
• No blank rows. Delete empty rows between the header and the data, and after the last transaction.
• Keep files modest. Long histories are more reliable as one file per month or quarter.

Step 1: Extract the transactions

1. In aiXtractor, open Extraction and choose Bank statement (or leave Auto-detect on).
2. Upload the PDF, scan or photo. Statements up to 10 pages per file are joined into one list.
3. Review the table. Each value is linked to its place on the page: click it and the viewer highlights the source.

You get date, description, reference, debit, credit and balance for each transaction, plus the statement header: bank, account, period, opening and closing balances.

Step 2: Let the balances prove the extraction

On the Validation step, aiXtractor checks that the opening balance plus credits minus debits equals the closing balance, and recomputes the running balance on every row. If one digit was misread, the running balance breaks at that row and the check points straight to it.

This is the step that saves time later in Xero. If the statement reconciles here, any mismatch you see in Xero comes from the import settings, not from the data.

Step 3: Shape the CSV for Xero

Download the result as CSV (or Excel, if you prefer to edit there). Then:

1. Delete the header block at the top so the transaction table starts on row 1.
2. Add an Amount column: =credit − debit (for example =F2-E2). Payments become negative, deposits positive.
3. Rename date to Date, description to Description and reference to Reference.
4. Delete the original debit, credit and balance columns.
5. Format the date column to your organisation's format, for example dd/mm/yyyy for a UK organisation.
6. Save as CSV UTF-8.

Doing this every month? Edit the bank statement extraction prompt once so it returns a signed amount column with Xero's headers, and steps 2 to 4 disappear. Each document type has its own prompt that you can edit; there are no hard-coded output formats to work around.

Step 4: Import into Xero

1. Go to Accounting → Bank accounts and pick the account.
2. Choose Manually import a statement (or Manage account → Import a statement).
3. Browse to your CSV and click Import.
4. Map the columns to Xero's fields if they were not matched automatically, then review the preview.
5. Save.

The imported lines appear on the Reconcile tab, ready to match.

When the import fails

"The file does not contain valid statement data." Usually trailing blank rows, a missing Date or Amount header, or a leftover header block. Delete everything that is not the transaction table.

"The date is not valid." The date format does not match your organisation's region, or the file mixes formats.

Payments came in as receipts. The signs are reversed. Check the Amount formula is credit minus debit, not the other way round.

Opening balance is off by one transaction. The statement's "balance brought forward" line was imported as a transaction. aiXtractor records it as the opening balance rather than a row, but check if you assembled the file another way.

Summary

Extract, reconcile, reshape, import. The reconciliation step is what makes this dependable: with every row's running balance checked, you know the data is right before Xero sees it. You can try it on your own statements with the free plan, which includes 50 pages a month.