Xero Bank Statement CSV Import Template Explained

The Xero bank statement CSV import template is a spreadsheet file, downloaded from Xero's Import a Statement screen, whose columns match Xero's bank statement fields. Only Date and Amount are required. Payee, Description, Reference and Check number are optional. Amounts sit in one signed column: money in positive, money out negative.

Required and optional columns in the Xero statement template

The Xero statement template has two required columns, Date and Amount, and four optional ones: Payee, Description, Reference and Check number. Required columns are marked with an asterisk in the downloaded file. Every row needs a date and an amount; the optional columns make reconciling easier because Xero has more to match on.

Xero's Import a bank statement in CSV format article explains where the template comes from. In the Accounting menu, select Bank accounts, click the account's menu icon, choose Import a Statement, and click the link to download the CSV template. The file is regional: its name and column headings follow the language set in your web browser, and it falls back to New Zealand English if Xero cannot tell. If the headings come out in another region's spelling, change the browser language and download again, or edit the headings in a spreadsheet.

The column table below summarises what Xero's article says about each field. "What goes wrong" describes what Xero does with a bad value, in plain terms rather than quoted error text.

Two more columns can be added to the template if a bank provides them. Analysis code holds the bank's own code for a line, which is different from the account code the transaction is later coded to. Transaction Type holds the bank's label for the kind of transaction, and it shows only on the reconcile screen.

There is also a second template. Xero's Import a precoded bank statement in CSV format article describes a version where each row also carries an account code and a tax rate display name. Xero then creates spend and receive money transactions and reconciles them in one step. The required fields there are Date, Amount, Account code and Tax Rate (Display Name). The same article warns that if the lines match invoices, bills or other transactions already in Xero, a precoded import introduces errors, and the lines should be imported to the Reconcile tab as ordinary statement lines instead.

Why does Xero misread dates in a bank statement CSV?

Xero misreads dates in a bank statement CSV when a date fits more than one format. A date like 03/04/2027 is valid as March 4 and as April 3. When Xero sees dates like that, it asks which format the file uses, then applies that answer to the whole file and to every later CSV import.

Xero's CSV article lists the date formats it reads. The main three are DD/MM/YYYY (15/01/2023), MM/DD/YYYY (01/15/2023) and YYYY/MM/DD (2023/01/15). It also accepts two-digit years, month abbreviations such as 15/JAN/2023, and dates separated by spaces, hyphens or periods.

Two situations cause most date trouble:

• Mixed formats in one file. A file pasted together from two sources can hold 03/04/2027 in one block and 2027-03-04 in another. Pick one format and convert every row to it.
• A saved answer from an earlier client. Because Xero remembers the confirmed format for later CSV imports, a file from a bank using the other order can import with every ambiguous date flipped. Xero's Change how Xero imports CSV bank statements article shows how to reach the mapping and date settings again through Import settings.

The safest choice for a template you build yourself is YYYY/MM/DD. It is on Xero's list and cannot be read two ways.

How should amounts be signed in a Xero bank CSV?

Amounts in a Xero bank CSV go in a single column, with money in as positive numbers and money out as negative numbers. A withdrawal of 30 dollars is written -30.00 or (30.00). Xero's article says not to use commas to show decimal places, and the precoded article adds that currency symbols should be left out.

The sign follows the bank account's point of view, not the client's books. A deposit raises the balance, so it is positive. A card purchase, a check or a transfer out lowers it, so it is negative.

Credit card accounts follow their own rule. Xero's CSV article says that for a credit card statement, payments to the card are positive and purchases are negative. Check how the card export prints them before importing. If purchases appear as positive charges, flip the sign on every row so they become negative.

Some bank exports and converted PDF statements use separate Debit and Credit columns instead. Before importing, combine them into one Amount column: credit minus debit, row by row. A spreadsheet formula such as =C2-B2, with debits in column B and credits in column C, does it in one pass. Delete the two original columns afterward so only one column can be mapped to Amount.

Leave out the balance column too. Xero's CSV article says to delete opening and closing balance columns, because Xero calculates the bank balance from the transactions.

Filling the Xero template from a converted PDF statement

Filling the Xero template from a converted PDF statement means moving each extracted transaction into the template's columns: the posting date into Date, the signed amount into Amount, and the merchant or counterparty into Payee. Delete balance rows, page totals and carried-forward lines first, since they are not transactions.

A PDF conversion can produce more than transactions: headers repeated on every page, "balance forward" lines, subtotals and blank rows. Xero's article says to delete balance columns, the bank account number and empty rows before import, and that a CSV cannot exceed 100,000 rows. How to get a clean spreadsheet out of a PDF statement in the first place is covered across the bank statements to Excel category.

A filled template with six rows

This example uses a fictional client, Fernhill Landscaping LLC, and its checking account ending XXXX-4821. All names and amounts are made up. The statement period is April 2027, and the file uses YYYY/MM/DD dates:

Date,Amount,Payee,Description,Reference,Check number
2027/04/01,2400.00,Ridgeview HOA,Deposit,INV 2041,
2027/04/03,-318.75,Greenline Nursery,Card purchase,Order 5520,
2027/04/08,-1150.00,Dale Okafor,Check paid,Crew payroll,1047
2027/04/12,-89.40,Example Fuel Stop,Card purchase,Truck 2,
2027/04/19,975.00,"Pine & Oak Properties, Inc.",Mobile deposit,INV 2044,
2027/04/30,-15.00,Bank,Monthly service fee,,

The six rows net to 1,801.85. That is the figure the April statement should show between its opening and closing balances. If it does not, a row is missing, doubled or signed the wrong way.

Row five shows why quoting matters. The payee name contains a comma, so it is wrapped in double quotes. RFC 4180, the common definition of the CSV format, says fields containing commas, double quotes or line breaks should be enclosed in double quotes. Without them, "Pine & Oak Properties" and "Inc." become two columns and every column after Payee shifts one place to the right.

When the file is ready, it goes in through the Import a Statement screen. The full click path, with what the review screen shows, is in the step-by-step Xero import guide.

Building the six rows is quick; building six hundred from a year of scanned statements is not. Schema CPA reads a client's checking, savings and credit card statements, flags a month missing from the run, and hands back the transactions as a CSV or Excel file that the firm reshapes into these columns. The workflow section of the homepage shows where export fits.

Xero CSV template problems with references and payee names

Problems with references and payee names in a Xero CSV usually come from text that does not match what Xero already holds, or from characters that break the file's columns. A payee spelled differently from the existing contact can create a second contact. An unquoted comma in a reference pushes later values into the wrong columns.

Xero's CSV article gives the payee rule directly: if the payee is an existing contact, the name in the file must match the contact name in Xero exactly, to avoid creating duplicates. "Greenline Nursery" and "Greenline Nursery Inc" are two different contacts to Xero. Before import, compare the payee column against the client's contact list and correct the spelling in the file.

Bank exports make this harder when they lack a clean payee. A card line can read like "GREENLINE NURS 5520 SPRINGFIELD", with a store number and city attached. Put the cleaned name in Payee and keep the raw bank text in Description, where it still helps matching but cannot create contacts.

References break the file in a different way. A reference such as INV 2041, partial contains a comma. Unquoted, it splits into two fields and the Check number column receives text. Wrap any field containing a comma or a double quote in double quotes, and write a double quote inside a field as two double quotes, as RFC 4180 describes.

A short check before every upload catches most of these:

• Count the columns on each row in a text editor; every row should match the header.
• Sort by Payee and look for near-duplicates of the same name.
• Confirm the Amount column holds numbers only, without currency symbols, and uses a period for decimals.
• Total the Amount column and compare it with the statement's change in balance.

Firms that run the same clients through QuickBooks face the same cleanup with different column rules. The routing guide for exporting to QuickBooks Online shows which QuickBooks import fits each kind of client file, and the guide to importing CSV into QuickBooks covers the list and invoice templates.