Xero CSV import failed: fixing column mapping and date errors

What Xero needs from a statement CSV

Xero’s help says only two fields are needed to create a statement line: Date and Amount. Payee, Description and Reference are optional, but they make reconciliation much easier, because Xero’s matching and bank rules read them. The header row must be row 1, and each column name should be unique. Xero offers its own template: Accounting → Bank accounts, the three-dot menu on the account, Import a Statement, then the link to download the CSV template.

Xero’s help also asks you to remove what a bank export often adds: opening and closing balance columns (Xero calculates the balance from the transactions), the bank account number, and empty rows. A CSV is limited to 100,000 rows. A rejected file is therefore usually rejected for the shape of a column, not for the columns present.

“The file you uploaded does not contain valid statement data”

The file you uploaded does not contain valid statement data.

This is Xero’s catch-all for a CSV it could not turn into statement lines. Xero’s own troubleshooting gives the date format as one cause; the others below are the shapes that produce it most often, in rough order of likelihood.

• The date format set for the import does not fit the dates in the file, often because Xero reused a format saved from an earlier CSV with the same headers.
• A column is mapped to a field whose data does not fit, such as the transaction date mapped to Payee. Xero names this field mismatch as a common cause.
• The header row is not row 1. Bank exports often carry an account summary above the table.
• Empty rows, usually page breaks from a converted PDF or a blank line at the end of the file.
• An amount column containing text: currency symbols, CR and DR suffixes, or a comma used as the decimal point.

1. 1Open the CSV in a text editor rather than Excel, so you see what Xero sees.
2. 2Delete everything above the header row and every blank line, including the last one.
3. 3Check that every date in the column uses the same pattern.
4. 4Check the amount column holds only numbers, a minus sign or brackets, and a full stop for decimals.
5. 5Import again, and read the mapping and date-format screen carefully rather than clicking through it.

The amount column defeats most files

Bank statements typically print two amount columns, debit and credit, and leave one blank on each row. Xero’s CSV help is clear that income and expense amounts must be in the same column: income positive, expenses negative, written either with a minus sign or in brackets, so -30.00 and (30.00) are both accepted. Commas must not be used as the decimal point.

• One signed column: negative for money out, positive for money in. This is what Xero asks for and the least ambiguous choice.
• Two columns from the bank: combine them before importing. In a spreadsheet, a new column of =C2-B2 (credit minus debit) gives the signed amount; check blanks are blank, not text.
• Columns of zeros: a debit column with 0.00 on every credit row produces lines of nothing when combined carelessly. Combine with a formula, not by hand.
• A balance column: delete it. Mapped by mistake it becomes a column of large phantom transactions.

Credit card statements need particular care. A card statement prints purchases as positive numbers, but in Xero money spent on the card is still money out and must be negative. If every purchase comes in as a receipt, the signs were taken from the PDF as printed.

The date setting that shifts everything by months

A CSV date carries no indication of its order. 03/04/2026 is 3 April to half the world and 4 March to the other half. Xero reads DD/MM/YYYY, MM/DD/YYYY and YYYY/MM/DD among other patterns, and where a date could be read more than one way it asks you to confirm the format. It then uses that format for the whole statement and for future CSV statements with the same headers.

1. 1Look down the date column for a first number above 12. If there is one, the file is day-first.
2. 2On the import screen, choose that order explicitly rather than accepting what Xero offers.
3. 3If a previous import saved the wrong format, change it in the import settings; Xero’s help article “Change how Xero imports CSV bank statements” covers where.
4. 4After importing, open the statement lines and compare the first and last dates with the statement period on the PDF.

When the import succeeds but the numbers are wrong

• Lines in the wrong month: the date order. Delete the imported statement and import again with the right format.
• Every amount reversed: the signs. Common with card statements and with a debit column mapped as Amount.
• Half the statement missing: one of two amount columns was mapped and the other ignored.
• Nothing from before a certain date: Xero’s import starts from the last transaction already imported unless you set the date range.
• Imported balance differs from the bank: a line was dropped or doubled before the file reached Xero.

Statement lines are not bank transactions

Xero draws a distinction worth understanding before troubleshooting an import. What you upload becomes statement lines: the bank’s side of the story, sitting in the reconciliation screen waiting to be matched. They are not accounting transactions until you reconcile them against a bill, an invoice or a spend-money entry.

So an import that “worked” but shows nothing in your reports has not failed. The lines are there; they have not been reconciled yet.

Duplicates, and undoing a bad import

Xero checks an incoming file against statement lines already on the account, but without transaction IDs to work from its matching rests on dates and amounts, so the check is heuristic. Two genuine transactions on the same day for the same amount, a pair of identical fees say, look alike. For lines already in, Xero’s Duplicate Statement Lines report lists lines with the same date and amount so you can remove the extras.

Undoing an import is easier than repairing one. Xero’s help explains that an individual deleted statement line stays on the statement and can be restored, and that a whole statement imported in error can be deleted from the account’s bank statements list. Delete it, fix the file, and import again. Do not reconcile lines from a bad import first, because reconciled lines have to be unreconciled before they can be removed.

Practical limits, and when to give up on CSV

• Split large files by month. It keeps the mapping screen readable and limits any mistake to one month.
• The account currency must match the file’s. Converting a statement does not convert its currency.
• Avoid overlap with the bank feed: import only the dates the feed does not cover.

OFX is self-describing: it states its dates in a fixed year-month-day form and its amounts are signed by definition. There is no mapping step because there is nothing to map, and Xero recommends OFX where it is available. If a statement has fought you through two attempts at CSV, the third attempt should be a different format.