Xero CSV Format Requirements: Complete Import Guide for Australian Bookkeepers (2026)
Xero requires CSV columns in this exact order: Date (DD/MM/YYYY), Amount (positive for credits, negative for debits, two decimal places), Payee, Description, Reference. Columns are read positionally, not by header name. ReckonFlow generates CSV files that meet all Xero format requirements automatically.
The Five Xero CSV Columns (Positional, Not Named)
This is the single most important thing to understand about Xero's CSV import: Xero reads columns by position, not by header name. It doesn't matter what you name your columns. What matters is what order they're in.
| Column | Position | Content | Example |
| A | 1st column | Date | 30/06/2026 or 2026-06-30 |
| B | 2nd column | Amount | 150.00 or -150.00 |
| C | 3rd column | Payee | Coles Supermarkets |
| D | 4th column | Description | EFTPOS PURCHASE 03/06 CARD 1234 |
| E | 5th column | Reference (optional) | INV-2026-001 |
If you put Amount in Column A and Date in Column B, Xero will try to parse the amount as a date, fail, and skip the row. No error message tells you why: it just shows a lower row count than expected.
Date Format Requirements
Xero's CSV importer accepts two date formats:
- DD/MM/YYYY (Australian standard): 30/06/2026
- YYYY-MM-DD (ISO 8601): 2026-06-30
What Xero does NOT accept:
- MM/DD/YYYY: This is the most common import failure. If your computer's regional settings are US English, Excel will silently convert DD/MM/YYYY to MM/DD/YYYY. The file looks correct when you open it, but Xero fails on every row.
- DD-Mon-YYYY: 30-Jun-2026 (accepted by some bank exports, but not by Xero's CSV import)
- DD.MM.YYYY: German-style dot separators
Safest approach: Use YYYY-MM-DD (ISO 8601). This format cannot be misinterpreted regardless of locale. Excel won't "helpfully" convert it. Xero parses it correctly. And it sorts alphabetically the same as chronologically.
EOFY warning: June 30 transactions that post on July 1. Always use the transaction date, not the post date. A June 30 transaction formatted as 01/07/2026 belongs in the wrong financial year.
Why Australian Bookkeepers Hit Date Format Errors
The chain of failure usually looks like this:
1. You download a bank statement PDF or CSV
2. You open it in Excel to clean up columns
3. Excel detects MM/DD/YYYY in your system locale and converts your DD/MM/YYYY dates
4. You save the file and upload to Xero
5. Xero rejects the date column silently: rows are skipped
6. You don't notice until reconciliation when the balances don't match
Fix: Open CSVs in a text editor (Notepad, VS Code, Sublime) before importing. If you must use Excel, import via Data → From Text/CSV and specify DD/MM/YYYY as the column format: don't just double-click the file.
Amount Format: Positive and Negative
Xero's CSV import uses a single Amount column (Column B). The convention:
| Transaction type | Amount format | Example |
| Money in (credit) | Positive number | 1500.00 |
| Money out (debit) | Negative number | -150.00 |
Important rules:
- No currency symbols. $150.00 will fail. Use 150.00.
- No thousand separators. 1,500.00 will fail. Use 1500.00.
- No brackets for negatives. (150.00) will fail. Use -150.00.
- Don't split into debit/credit columns. Xero's import expects a single Amount column, not separate Debit and Credit columns (some bank exports use this format: you must combine them before import).
Handling Different Bank Conventions
Australian banks use three different conventions for debits and credits:
| Bank | Debit format | Credit format | Conversion needed? |
| CBA | Separate Debit column | Separate Credit column | YES: consolidate into single Amount column |
| ANZ | DR indicator | CR indicator | YES: strip indicator, use negative for DR |
| Westpac | DR indicator | CR indicator | YES: same as ANZ |
| NAB | Negative amount | Positive amount | NO: already in single-column format |
| Macquarie | Negative or bracket | Positive | Sometimes: check for bracket convention |
If you're converting manually, this is where most transcription errors happen. A CBA statement with $150.00 in the Debit column and $0 in the Credit column should become -150.00 in the Amount column. If you accidentally swap the sign, you've created a phantom credit that will throw your reconciliation off by $300.
Payee and Description Handling
Columns C (Payee) and D (Description) are where your transaction details live. Xero uses these for matching against bank feed transactions during reconciliation.
| Column | What to put | Examples |
| C: Payee | The counterparty name | Coles Supermarkets, ATO, John Smith |
| D: Description | Transaction memo/reference | EFTPOS 03/06 CARD 1234, Salary Jun 2026, DIRECT DEBIT INSURANCE |
Tips for better reconciliation matching:
- Keep Payee consistent across imports. "Coles Supermarkets" and "COLES SUPERMARKETS" may not auto-match in Xero.
- Include the transaction date in the Description: Xero's matching algorithm weighs dates heavily.
- Don't truncate long descriptions if you can help it. The full reference helps match bank feed transactions later.
Reference Column (Optional)
Column E (Reference) is optional but useful:
- Cheque numbers: use the cheque number
- Invoice references: use your invoice numbering
- Transaction IDs: use the bank's internal transaction reference
Xero doesn't require this column. If you don't have references, leave it empty or omit it (Xero can handle a 4-column CSV with just Date, Amount, Payee, Description).
Header Row: Include or Omit?
Xero's CSV import works with or without a header row. If you include one, Xero ignores the header text and reads positions. A common convention:
```
Date,Amount,Payee,Description,Reference
30/06/2026,-150.00,Coles Supermarkets,EFTPOS PURCHASE 03/06,
01/07/2026,3000.00,Salary,PAYMENT RECEIVED,
```
The header row (Date,Amount,Payee,Description,Reference) will be skipped by Xero's parser. It's there for your reference when you open the file in Excel or a text editor.
Recommendation: Include the header row. It makes the file self-documenting and reduces errors when team members handle imports.
Common Xero CSV Import Errors (and Fixes)
"0 rows imported"
The most panic-inducing error. It usually means:
- Wrong date format. Check for MM/DD/YYYY. Convert all dates to DD/MM/YYYY or YYYY-MM-DD.
- Wrong column position. Date must be column A. If your CSV has extra columns before Date, remove them.
- Empty file. Check the file actually has data rows below the header.
"Only 45 of 50 rows imported"
Close but not complete. Possible causes:
- Blank rows in the CSV. Xero stops importing at the first completely blank row. Check for empty lines between data rows.
- Date parsing failures on specific rows. A single row with an unparseable date will be skipped, but the rest will import. Check the skipped rows for date format issues.
- Amount parsing failures. A row with $150.00 instead of 150.00 will be skipped.
"Closing balance doesn't match after import"
This isn't a Xero error: it's an import data error. Causes:
- Opening balance imported as a transaction. Your CSV includes the opening balance row as a debit or credit, inflating one side.
- Missing transaction. A multi-line description wasn't fully captured, and a row was dropped.
- Duplicate transaction. A transaction appears twice (once from PDF, once from bank feed).
Prevention: Run balance verification on your CSV before importing. Open the file, sum the Amount column, check it matches the statement's total credits minus total debits.
"Invalid file format"
The file isn't a valid CSV. Check:
- The file extension is .csv (not .xlsx, .txt, or .pdf)
- Columns are separated by commas, not tabs or semicolons
- Text values containing commas are properly quoted: "Coles, Supermarkets"
CSV Import vs Bank Feed: When to Use Each
| Scenario | Best method | Why |
| Daily transactions | Bank feed | Automatic, no manual work |
| Historical data (before feed start) | CSV import | Feeds don't backfill |
| New bank account | CSV import | Feed not yet available |
| Feed has gaps/broken | CSV import | Temporary fix while feed is repaired |
| EOFY bulk processing | CSV import | Feed pulls in real-time, not by statement period |
| Client provides PDF statements | PDF → CSV → import | Only option when client doesn't have feed |
The hybrid approach: use feeds for what feeds do well (daily sync, ongoing reconciliation) and CSV import for everything else. Run balance verification on every CSV import regardless of whether a feed exists.
Example: Correct Xero CSV Format
```
Date,Amount,Payee,Description,Reference
01/06/2026,5000.00,ACME Pty Ltd,INVOICE PAYMENT,INV-2026-100
03/06/2026,-150.00,Coles Supermarkets,EFTPOS 03/06,
05/06/2026,-45.00,"Officeworks, Auburn",OFFICE SUPPLIES,
10/06/2026,250.00,Refund,REFUND TRANSACTION,
30/06/2026,-2000.00,ATO,GST PAYMENT,PAYG-2026-Q4
```
Note: "Officeworks, Auburn" is quoted because the value contains a comma. Everything else works without quotes.
Frequently Asked Questions
What format does Xero expect for CSV bank statement import?
Xero expects a CSV file with 5 columns in this order: Date (Column A), Amount (Column B), Payee (Column C), Description (Column D), and Reference (Column E, optional). Dates must be DD/MM/YYYY or YYYY-MM-DD. Amounts are positive for credits and negative for debits, with no currency symbols or thousand separators.
Does Xero read CSV column headers or positions?
Xero reads columns by position, not header name. Column A is always Date, Column B is always Amount, regardless of what the header row says. You can include a header row (Xero will skip it), but the data columns must be in the correct positional order.
Why does Xero say "0 rows imported" for my CSV?
Almost always a date format issue. Check that your dates are DD/MM/YYYY or YYYY-MM-DD, not MM/DD/YYYY. Also verify that Date is Column A (not later in the file) and that there are no currency symbols in the Amount column.
Can I use a 4-column CSV without the Reference column?
Yes. Xero accepts a 4-column CSV with Date, Amount, Payee, and Description. The Reference column is optional. Just ensure the first 4 columns are in the correct order.
How do I convert an Australian bank statement PDF to Xero CSV?
Most Australian bookkeepers use a conversion tool that handles bank-specific formats automatically. ReckonFlow converts CBA, ANZ, Westpac, NAB, and Macquarie PDFs to Xero-ready CSV (with correct column positions and YYYY-MM-DD dates) and includes balance verification before export.
Why does my CSV work in Excel but fail in Xero?
Excel is lenient: it tries to guess what you meant. Xero is strict: it expects exactly the right format. The most common cause is that Excel displayed your dates as DD/MM/YYYY but stored them as MM/DD/YYYY internally. When you save the CSV and upload it to Xero, the internal format is what gets read, not the display format. Open the CSV in a text editor to see what Xero actually sees.
---
The easiest way to get the format right
Upload your bank statement PDF to ReckonFlow and get a Xero-ready CSV with the correct column order, date format, and verified balance. No spreadsheet setup needed.