Prepare a bank CSV for Xero: get dates and payment signs right

Start with the account, not the file

This guide covers ordinary bank-statement CSV imports, not precoded transactions. We read Xero Central with United States, Canada, United Kingdom, Australia and New Zealand selected on October 17, 2026, but didn't test live imports, other regions or account-specific entitlements. This independent guide and checker aren't affiliated with or endorsed by Xero.

A well-formatted CSV can still go into the wrong account or repeat an already-imported period. Check the destination account, statement currency and missing dates before editing. Keep bank accounts and cards separate, and find where the feed or previous upload stops so you know which dates you need.

Your bank may already offer an export covering those dates. The Xero CSV guide, checked October 17, 2026, recommends bank-native OFX, QFX or QuickBooks files when available, or CSV for your own import. To import, you need the administrator or standard plus bank accounts user role.

If you only have PDFs, Xero's current Hubdoc extraction guide covers scanned and downloaded statements. That native CSV option may save you using another converter, so check its statement requirements and your account's import options first.

Match your columns to the Xero fields

You don't need to rename every column to match Xero. The CSV guide requires only Date and Amount, with income and expenses in the same Amount column. Headings are optional, though Xero recommends unique headings in row 1 to make mapping easier. Custom headings are fine as long as you assign their contents to the right Xero fields.

Description appears under Particulars in the Bank statements tab, while Analysis code is the bank's code, not your reconciliation account code. For Payee, match an existing contact's name exactly to avoid creating a duplicate contact. That's a different problem from a duplicate statement transaction.

The US template linked by Xero's error guide contains *Date,*Amount,Payee,Description,Reference,Check Number. The asterisks mark required fields, but aren't mandatory bank-export headings. For a manual file, download the template from your own import screen.

Xero chooses the template region from your browser language, defaulting to New Zealand English if it can't determine it. That doesn't tell you how your bank wrote 03/04/2026. As the guide directs, remove balance columns, the bank account number and empty rows from the import copy, but leave them in the original for review.

Work out the date order before changing it

Your organization's region doesn't settle the date order. All five regional editions we opened list DD/MM/YYYY, MM/DD/YYYY and YYYY/MM/DD. They don't say US organizations must use month-first dates or UK organizations day-first.

These formats come from the US page and the regional page with each country selected. If you're comparing the rules, select your region and wait for the article to reload.

The guide also lists two-digit years, abbreviated month names, day-first hyphenated or dotted dates, space-separated dates and compact DDMMYYYY. The checker is narrower: it requires a four-digit source year because it won't guess the century. For compact DDMMYYYY choose DMY, or MDY for MMDDYYYY. It follows that choice and exports one of the three four-digit-year slash formats above.

A transaction whose day exceeds 12 can reveal the order, or you can compare with the printed statement. If every date is ambiguous, the CSV can't settle it. Xero says it prompts for ambiguous dates, then applies your choice to this statement and future CSV bank statements. If saved mappings take you straight to Review, its mapping guide explains how to return to Import settings. Recheck those settings when your bank changes its export, since the saved choice may no longer fit.

Make the amount signs mean what Xero expects

For a bank account, Xero's amount instructions say income is positive and expenses negative. A negative can use a leading minus or brackets: -30.00 or (30.00). Decimal commas aren't allowed. The checker exports decimal points without grouping separators or currency symbols, but we haven't tested every grouped spelling in Xero.

An amount such as 1,234 doesn't tell you whether the comma means thousands or decimals, so choose the source convention before removing punctuation. Keep the exact precision too: if an amount looks unfamiliar, check why it differs from the statement before rounding it. For separate debit and credit columns, check what each means for this account before combining them.

A known purchase and payment give you something concrete to check in Xero's preview. The checker only reverses every amount if you choose to, and shows each change. If the file mixes conventions, reversing everything just moves the error. It doesn't infer refund treatment or money direction from merchant names, so unusual rows still need checking against the statement and review screen.

What changes in a prepared CSV

This is the fictional March 2026 checking account from our statement-checking guide, not a customer file. Its opening balance is 2,400.00 USD. The source uses month/day/year and decimal points, with alternative headings, date spaces, grouped amounts and bracketed negatives.

Posted,Details,Value,Reference
 03/02/2026 ,"Deposit, client invoice","1,200.00",INV-0302
03/03/2026,Rent,"(1,150.00)",RENT-MAR
03/07/2026,Office supplies,(86.45),SUP-0307
03/11/2026,Refund received,42.50,REF-0311
03/18/2026,Software subscription,(29.00),SOFT-MAR
03/31/2026,Monthly service fee,(12.00),FEE-MAR

Here, Posted maps to Date, Value to Amount, Details to Description and Reference to Reference. For this source, choose MDY, decimal point, existing Xero signs and output YYYY/MM/DD. The checker's Load example button loads these rows and choices, though you still need to approve the download.

Date,Amount,Description,Reference
2026/03/02,1200.00,"Deposit, client invoice",INV-0302
2026/03/03,-1150.00,Rent,RENT-MAR
2026/03/07,-86.45,Office supplies,SUP-0307
2026/03/11,42.50,Refund received,REF-0311
2026/03/18,-29.00,Software subscription,SOFT-MAR
2026/03/31,-12.00,Monthly service fee,FEE-MAR

The columns have moved, but no transactions were removed. Dates lost their spaces and changed format without changing meaning, while amounts lost grouping commas and gained minus signs in place of parentheses. CSV quotes still protect the comma in the deposit description, and the references are unchanged.

The six transactions total 1,242.50 in and 1,277.45 out, giving a net movement of -34.95. Adding that to 2,400.00 gives the printed closing balance of 2,365.05. These amounts were independently checked with decimal arithmetic. The format checker doesn't compare balances, so that check belongs in the separate arithmetic tool. Even matching totals can't detect two offsetting errors or authenticate a statement.

When Xero reports an import error

“No valid statement data” can point to several problems. The Xero import-error guide, checked October 17, 2026, lists invalid date settings, leading or trailing date spaces, blank lines and badly prepared or corrupt data as causes. If you see it, prepare the file again, copying into a fresh template if needed. Xero also can't import zero-value transactions, but review those rather than silently deleting them.

Repeated headers and descriptions or payees resembling subtotal or balance labels will block the checker. Actual summaries don't belong in the import copy, but a genuine transaction does: correct its label against the source rather than removing the row.

Xero's error guide gives a 255-character Reference limit and a 20-character Check number limit. The checker reports rows without truncating identifiers. It protects formula-like text with an apostrophe prefix in the download and reports that change. Check the prefix in Xero, especially when a Payee must match an existing contact exactly.

Xero's pages give different size limits. The CSV guide says files above 100,000 rows can't import, while troubleshooting says to split above 1,000 bank transactions. Hubdoc documents Xero CSV batches of 1,000. Our checker caps corrected downloads at 1,000 transaction records; its 5 MB local read limit isn't an established Xero CSV byte limit.

Unfamiliar characters aren't a reason to strip text from the file. Xero's non-Roman character guide describes saving Unicode Text from Excel or CSV from Google Sheets, but warns that some characters may not be fully supported. Excel's Unicode Text is for direct Xero import: it can produce UTF-16, while the checker accepts only UTF-8. The checker preserves Unicode and flags non-ASCII text for review, but blocks replacement or control characters rather than guessing repairs. Don't remove accents or non-Roman names just to silence a warning. You don't need to upload to Google Drive to use this local checker.

Finish the review inside Xero

An unfamiliar layout is better tested with fabricated transactions in an isolated test organization and account, separate from your live books. If a live preview looks wrong, cancel before Complete import and fix a copy of the CSV. Don't import just to see what happens.

The documented import flow starts at Accounting, Bank accounts, then the intended account's menu and Import a Statement. After uploading the file, map the fields and confirm dates when prompted. The review is your chance to check rows ready to import, warnings and errors, and clear unwanted transactions before choosing Complete import.

Xero says it won't import transactions matching ones already present, but it doesn't explain its matching algorithm. Don't assume date and amount are enough. The local checker only flags rows with matching exported fields within this file. Identical purchases can be genuine, and without account access no local tool can inspect earlier Xero lines.

If you're re-importing, first check what succeeded last time. Deleting and re-uploading isn't a general reset: the Xero delete-or-restore guidance says deleted lines remain restorable and deleting a line changes the Statement Balance. Deletion is for removing a duplicate, not fixing a wrong reconciliation. If you deleted a line by mistake, use the restore flow.

Before completing the import, the account, currency and date range should agree with the source, as should every transaction, including the first, last and unusual rows. Proposed fixes, omitted columns and duplicate candidates all need review, and dates and payment direction need checking in Xero rather than only in the checker. Keep a record of the import with your source and working copy. The books still need reconciling separately.

If your starting point is a PDF

For scanned and downloaded debit or credit statements, the Hubdoc guide sets out the requirements: English, every page of one statement, no more than 100 pages, at most 35 MB and at least 200 DPI. It describes Generate CSV, Download and optional transaction highlighting to help review errors. Bank support doesn't guarantee every layout, and the guide cautions about non-supported banks.

You may only need a bank-native export or a carefully prepared manual CSV. If you need separate PDF extraction, AnyStatement offers PDF statement conversion. That service uploads files, whereas the free CSV checker never uploads or stores yours. Whichever route you take, inspect the final Xero import and reconcile your books.

What we checked and left untested

On October 17, 2026, we opened Xero Central in a browser, expanded the preparation and import sections and selected all five regions above. We also opened the linked troubleshooting, saved-mapping, deletion/restoration, non-Roman character and Hubdoc pages, plus the US CSV template. Python Decimal verified every example amount, total and balance. No customer records were used.

We didn't import into Xero or test permissions, subscription eligibility or contact matching, and we didn't establish undocumented duplicate rules. Vendor guidance, local format checks and example arithmetic are separate checks. None amounts to a claim of Xero acceptance, bookkeeping reconciliation, statement authenticity or fraud detection.