Most bad Xero bank imports can be caught before you click Import, with a few checks on the CSV: every date parses strictly in the format you'll confirm in Xero, every amount is a plain signed number, and the opening balance plus the rows equals the statement's printed closing balance. Add a duplicate check on date, amount and description and you've covered most of what goes wrong. Below is a dependency-free Node script that does all of it in under 80 lines.
I'm Damin and I run ZeraBooks, which converts PDF bank statements into Xero-ready CSVs, so I've spent more time than is healthy looking at these files. This script is a standalone sketch, not our production code, and it works on any CSV in the same layout.
What layout is it checking?
Xero's Import a Statement takes income and expenses in one Amount column, positive in and negative out, and asks you to strip balance columns and account numbers because it works out the balance from the lines. The layout here has five columns:
Date,Amount,Payee,Description,Reference
Dates are MM/DD/YYYY. Amounts have two decimals, a leading minus for money out, and no thousands separator or currency sign. That's the layout ZeraBooks writes for its Xero-ready format, and it's what the checker enforces.
The sample file
A fictional account ending 5520 for May 2026. The printed statement opens at 3,902.75 and closes at 5,678.50. Two things are wrong with this export, on purpose:
Date,Amount,Payee,Description,Reference
05/01/2026,-850.00,Willow Lane Storage,ACH DEBIT WILLOW LANE STORAGE,
05/04/2026,1200.00,Tallis Group,ACH CREDIT TALLIS GROUP,
05/12/2026,-64.30,Fuelstop,POS FUELSTOP 2231,
05/12/2026,-64.30,Fuelstop,POS FUELSTOP 2231,
05/20/2026,"1,500.00",Harrow Design,ACH CREDIT HARROW DESIGN,
05/31/2026,-9.95,Monthly Fee,MONTHLY MAINTENANCE FEE,
The script
// check-xero-csv.js: Node 18+, no dependencies
const fs = require('fs')
const HEADER = ['Date', 'Amount', 'Payee', 'Description', 'Reference']
// Split one CSV line, honouring double quotes ("a,b" stays one field)
function parseLine(line) {
const out = []
let cur = ''
let quoted = false
for (let i = 0; i < line.length; i++) {
const ch = line[i]
if (quoted) {
if (ch === '"' && line[i + 1] === '"') { cur += '"'; i++ }
else if (ch === '"') quoted = false
else cur += ch
} else if (ch === '"') quoted = true
else if (ch === ',') { out.push(cur); cur = '' }
else cur += ch
}
out.push(cur)
return out
}
// Strict MM/DD/YYYY: 02/30/2026 is rejected, not rolled into March
function parseDate(s) {
const m = /^(\d{2})\/(\d{2})\/(\d{4})$/.exec(s)
if (!m) return null
const [mm, dd, yyyy] = [Number(m[1]), Number(m[2]), Number(m[3])]
const d = new Date(Date.UTC(yyyy, mm - 1, dd))
return d.getUTCMonth() === mm - 1 && d.getUTCDate() === dd ? d : null
}
// Plain signed number with two decimals, returned as integer cents
function toCents(s) {
if (!/^-?\d+\.\d{2}$/.test(s)) return null
const neg = s.startsWith('-')
const [whole, frac] = s.replace('-', '').split('.')
const cents = Number(whole) * 100 + Number(frac)
return neg ? -cents : cents
}
function check(file, openingCents, closingCents) {
const lines = fs.readFileSync(file, 'utf8').trim().split(/\r?\n/)
const problems = []
const header = parseLine(lines[0])
if (header.join() !== HEADER.join()) problems.push(`header is "${header.join()}"`)
const seen = new Map()
let total = 0
let rows = 0
lines.slice(1).forEach((line, i) => {
const n = i + 2 // line number in the file, header is line 1
const [date, amount, payee, description] = parseLine(line)
if (!parseDate(date)) problems.push(`line ${n}: date "${date}" is not MM/DD/YYYY`)
const cents = toCents(amount)
if (cents === null) {
problems.push(`line ${n}: amount "${amount}" is not a plain signed number`)
return
}
if (!payee) problems.push(`line ${n}: no payee`)
const key = `${date}|${cents}|${description}`
if (seen.has(key)) problems.push(`line ${n}: same date, amount and description as line ${seen.get(key)}`)
else seen.set(key, n)
total += cents
rows++
})
const gap = openingCents + total - closingCents
if (gap !== 0) problems.push(`opening + ${rows} rows misses the closing balance by ${(gap / 100).toFixed(2)}`)
return problems
}
const [file, opening, closing] = process.argv.slice(2)
const problems = check(file, toCents(opening), toCents(closing))
console.log(problems.length ? problems.join('\n') : 'ok: ready to import')
process.exitCode = problems.length ? 1 : 0
A few choices worth explaining.
Integer cents. Every amount becomes a whole number of cents before any arithmetic. In floating point, 0.1 + 0.2 is 0.30000000000000004, and over a few hundred bank lines a balance check can fail by a fraction of a cent that isn't real. Cents make equality exact.
Strict dates. new Date(Date.UTC(2026, 1, 30)) quietly rolls over to March 2. The check rebuilds the date and confirms the month and day survived, so 02/30/2026 is rejected rather than moved.
A strict amount pattern. /^-?\d+\.\d{2}$/ refuses "1,500.00", "$1500.00", "(1500.00)" and "1500". Xero accepts brackets for negatives, but a file that mixes conventions has usually been edited by hand, and that's worth knowing.
Duplicates are flagged, not removed. Two identical fuel purchases on the same day can both be real. The script only says the pair exists. The balance check decides.
Running it
$ node check-xero-csv.js may-5520.csv 3902.75 5678.50
line 5: same date, amount and description as line 4
line 6: amount "1,500.00" is not a plain signed number
opening + 5 rows misses the closing balance by -1564.30
Read the gap before anything else. It's -1,564.30: the 1,500.00 deposit that couldn't be parsed, plus one 64.30 fuel charge too many. Fix the amount to 1500.00 and run again:
$ node check-xero-csv.js may-5520.csv 3902.75 5678.50
line 5: same date, amount and description as line 4
opening + 6 rows misses the closing balance by -64.30
Now the gap is exactly one fuel charge, which settles the duplicate question: the printed balance says only one of those two lines is real. Delete line 5:
$ node check-xero-csv.js may-5520.csv 3902.75 5678.50
ok: ready to import
The gap-size rule generalises. A gap equal to one line means that line is missing or doubled. A gap of twice a line means it went in with the wrong sign. A gap equal to the opening balance means the opening was taken as zero.
What it can't catch
Day-first organisations. A file can be valid MM/DD/YYYY and still be misread if the Xero organisation reads dates day first, as UK, Australian and New Zealand ones usually do: 05/04/2026 imports as 5 April. The fix is in Xero's column assignment, where you confirm the date format. The script can't see this from the file. One cheap warning to add: if every day in the file is 12 or under, a day-first import will misread every date without a single error.
Missing statements. The balance check is per statement. If you're importing a year, also check that each statement's opening balance equals the previous statement's closing balance, and import in date order.
Payee names. Xero matches payees to existing contacts by exact name. The script checks that a payee exists, not that it matches your contact list.
Where the input usually comes from
Disclosure: I'm Damin and I run ZeraBooks, so weigh this part accordingly. ZeraBooks Convert reads PDF bank statements and downloads a Xero-ready CSV in exactly this layout, after proving each account against the statement's printed opening and closing balances to the cent. It doesn't connect to Xero; you import the file yourself. It's part of the ZeraBooks subscription, 1,000 pages a month for $79 a month billed annually, with a 7-day trial.
The full walkthrough of the Xero side, with a sample file and the usual causes of messy imports, is in our guide to importing a bank statement into Xero. Other formats and document types are on the bank statement converter page.
If you build your own version, keep the cents and keep the gap in the output. A boolean tells you the file is wrong. The gap usually tells you where.
Top comments (0)