How to validate an accounting CSV before QuickBooks or Xero import
- Step 1Export the accounting CSV — Download the bank-statement, invoice, customer, or general-ledger CSV from your bank portal or accounting workflow. If you edited it in Excel, save with Save As → 'CSV UTF-8 (Comma delimited)' so accented vendor names survive. The validator reads text CSVs only — convert any
.xlsxto CSV first. - Step 2Drop the file onto the validator above — Free tier: 2 MB / 500 rows; Pro: 100 MB / 100,000 rows. A 500-row cap covers a typical month of bank lines but not a full year — split a 12-month export with csv-row-splitter on free tier. The dropzone accepts
.csv,.tsv, and.txt, and validation runs automatically when the file lands. - Step 3Check the four summary tiles — Read Health score, Rows (lines including the header), Columns (the expected width), and Issues (errors + warnings). For accounting files the number that matters is
errors: anyrow_widthorencodingerror will likely sink the whole import in QuickBooks or Xero, regardless of a high overall score. - Step 4Clear the errors in the Top issues panel first — The panel lists the first 8 issues. Red
errorbadges (row_width,encoding) are import-blockers; amberwarningbadges (type_mismatch,duplicate_key,empty_cell,duplicate_header,duplicate_row,suspicious_value) are quality flags. For each, the detail string names the column and the offending value so you can locate the exact transaction. - Step 5Read the Column profile for the money and date columns — Confirm
Amount/Debit/Creditshow asnumberandDateshows asdate. Astringormixedprofile on a money column means most cells aren't clean numbers — usually a currency symbol or text throughout. AmixedDatecolumn usually means two date formats are present (e.g.2025-01-05mixed with05/01/2025). - Step 6Fix in source, download the report, re-validate — Use 'Download report' for a
<filename>.health-report.jsonaudit trail, then fix in your spreadsheet: strip currency symbols and normalise decimals with csv-find-replace, remove a double-imported transaction with csv-deduplicator on theTransaction IDcolumn, and re-save as CSV UTF-8. Re-drop to confirmerrorsis zero, then import into QuickBooks or Xero.
What the validator checks on an accounting CSV
Eight issue types, mapped to accounting-import failure modes. Error severity is an import-blocker; warning severity is a data-quality flag worth reviewing.
| Issue type | Severity | What triggers it | Why accounting files hit it |
|---|---|---|---|
| `row_width` | Error | A row's column count differs from the header's column count | An unescaped comma inside a Description / Memo / Payee field splits the row, shifting every amount one column right |
| `encoding` | Error | Cell contains \uFFFD or a control character below 0x20 | A CP1252 Excel save read back as UTF-8 — accented vendor/payee names (Café, Müller, Société) come back as garbage |
| `type_mismatch` | Warning | A cell doesn't match the column's dominant number / date / boolean profile (85% dominance) | £125.00 or N/A or pending in Amount; 05/01/2025 mixed with ISO 2025-01-05 in Date |
| `duplicate_key` | Warning | Same value in a column whose name matches the key heuristic (Transaction ID, Invoice Number, Reference, Ref, names with id) | A transaction or invoice imported twice — duplicate reference numbers double-count in the ledger |
| `duplicate_row` | Warning | A whole data row matches an earlier one after trimming whitespace and lowercasing | Re-pasting the same statement line, or overlapping date ranges in two exports |
| `empty_cell` | Warning | An empty cell in a column that's at least 85% populated | A skipped Amount or Date mid-file; sparsely-used columns (e.g. Memo) stay quiet because they exceed 15% empty |
| `duplicate_header` | Warning | The same header name appears twice in row 1 (case-insensitive) | Two Amount columns, or Date and date, from merging two bank templates |
| `suspicious_value` | Warning | A once-only value substring-similar to a common value, in a 2–12 distinct-value column | Debit (trailing space) next to Debit, or EUR vs Euro, in a Type / Currency column |
How the number classifier reads accounting values
What counts as a clean number versus a type_mismatch. The classifier normalises before testing: strips a leading sign+$, removes commas, drops a trailing %, and converts bracketed (x) to -x.
| Cell value | Read as | Notes |
|---|---|---|
125.00 | number | Plain decimal — always fine |
$1,299.50 | number | Leading $ stripped, commas removed |
(125.00) | number | Bracket form converted to -125.00 (accounting negative) |
-125 | number | Leading minus accepted |
12.5% | number | Trailing % dropped before the numeric test |
£125.00 | string -> type_mismatch | Non-dollar currency symbol is NOT stripped — flagged in a numeric column |
125,00 | string -> type_mismatch | EU comma-decimal: the comma is removed, leaving 12500 — a different value; flagged |
N/A / pending / -- | string -> type_mismatch | Placeholder text in an amount column |
2025-01-05 | date | ISO date — recognised as date, not number |
05/01/2025 | date | Slash date — recognised; mixing ISO and slash in one column yields mixed |
What the validator catches vs what QuickBooks / Xero catches
Structural pre-flight here; business-rule validation in your accounting tool. Both layers matter.
| Check | JAD CSV Validator | QuickBooks / Xero import |
|---|---|---|
| Text or symbol in an amount column | Yes — type_mismatch warning before import | Yes — rejects the file, often without a row number |
Row split by comma in Description | Yes — row_width error before import | Yes — misaligned amounts, rejected or mis-posted |
| Encoding mojibake in payee names | Yes — encoding error before import | Often imports garbled silently |
Duplicate Transaction ID / Invoice Number | Yes — duplicate_key warning | May double-post the transaction |
A required column missing (no Amount) | No — profiles only present columns | Yes — import wizard requires the field mapping |
| Debits and credits balance / reconcile | No — no accounting logic | Yes — reconciliation is the accounting tool's job |
| Date is a valid bookable period | No — only format is checked | Yes — locked-period rejection happens in the tool |
| File size cap | Free 2 MB / Pro 100 MB | QuickBooks bank-CSV import caps around 1,000 rows / 350 KB per upload |
Cookbook
Real bank-statement and ledger scenarios mapped to the exact validator output. Each code block is a representative excerpt; column names follow common QuickBooks/Xero import conventions.
Currency symbol and EU decimal in an Amount column
ExampleThe single most common accounting-import failure is text in the money column. The classifier accepts $1,234.56 but not £ or a comma-decimal, so those cells are flagged against the inferred number profile.
Header:
Date,Description,Amount
Rows:
2025-01-03,Office supplies,125.00 OK
2025-01-04,Client lunch,$1,299.50 OK ($ stripped, comma removed)
2025-01-05,Refund,(45.00) OK (bracket = -45.00)
2025-01-06,Subscription,£12.99 type_mismatch (non-$ symbol)
2025-01-07,Bank fee,12,50 type_mismatch (EU comma decimal)
Validator output (2 issues):
Row 5: Expected number based on the column profile, but found "£12.99".
Row 6: Expected number based on the column profile, but found "12,50".
Fix: csv-find-replace regex [£$€¥] -> (empty), then (\d+),(\d{2})$ -> $1.$2
to convert EU comma decimals to point decimals.Comma inside a Description splits the row
ExampleBank-statement descriptions often contain commas. If the export didn't quote the field, the row gains an extra column and every amount shifts right. The validator reports a row_width error.
Header (4 cols):
Date,Description,Debit,Credit
Row (5 cols — comma inside the description):
2025-02-11,PAYMENT, INVOICE 4471,0.00,1200.00
^ this comma split the description
Validator output:
Row 2, Col 1 (row width)
type: row_width severity: error
detail: "Row has 5 columns; header has 4."
Fix: re-export from the bank (most portals quote descriptions), or wrap
the Description column in double quotes before saving.Duplicate Transaction ID from a double import
ExampleImporting two overlapping date ranges, or re-running an import, duplicates transactions. Because Transaction ID contains the id token, it's checked as a unique key and the repeat is flagged.
Header:
Transaction ID,Date,Amount
Rows:
TXN-00481,2025-03-01,250.00
TXN-00482,2025-03-02,75.00
TXN-00481,2025-03-01,250.00 <- same transaction imported twice
Validator output:
Row 4, Col 1 Transaction ID
type: duplicate_key severity: warning
detail: 'Value "TXN-00481" also appears on row 2.
This column looks like a unique key.'
Fix: de-dupe with csv-deduplicator on the Transaction ID column before
importing, or find duplicates first with csv-duplicate-finder.Mixed date formats yield a 'mixed' column
ExampleWhen a date column contains both ISO (2025-01-05) and slash (05/01/2025) formats, the dominant type can't clear 85%, so the column profile becomes mixed and downstream date checks don't fire — but the mixed profile itself is the warning sign.
Header:
Date,Amount
Rows:
2025-01-05,100.00 classified: date (ISO)
06/01/2025,200.00 classified: date (slash)
2025-01-07,150.00 classified: date (ISO)
08/01/2025,175.00 classified: date (slash)
Column profile:
Date -> mixed (no single date format reaches 85% dominance,
so the column is reported as mixed, not date)
Fix: normalise to one format in source. QuickBooks/Xero want a single,
consistent date format across the whole file; pick ISO (YYYY-MM-DD)
to avoid day/month ambiguity, then re-validate to get a clean 'date'.Mojibake in vendor names from a CP1252 save
ExampleSaving a statement in Excel-for-Windows as plain CSV can write CP1252. The browser reads the file as UTF-8, so CP1252 bytes for accented letters surface as the replacement character and are flagged as encoding errors.
Header:
Date,Payee,Amount
Rows (CP1252 decoded as UTF-8):
2025-04-02,Caf\uFFFD Central,42.00 encoding error (was 'Café')
2025-04-03,M\uFFFDller GmbH,310.00 encoding error (was 'Müller')
Validator output:
Row 2, Col 2 (Payee): Cell contains a replacement or control character,
which usually means an encoding problem.
Row 3, Col 2 (Payee): same.
Fix: re-save as 'CSV UTF-8 (Comma delimited)', or open in a UTF-8-aware
editor and re-save. csv-cleaner can also normalise the text.Errors and edge cases
Real errors and silent failures sourced from each platform's own documentation. Match the wording to the row, fix what the row says to fix.
Bracketed negatives like `(125.00)` are read as numbers, not errors
By designAccounting exports often write negatives in parentheses. The number classifier converts (125.00) to -125.00 before testing, so a Debit or Amount column full of bracketed figures is correctly inferred as number and no type_mismatch is raised. This means you won't get false alarms on standard ledger formatting — but it also means the validator won't warn you if a bracket was a typo; it only checks numeric shape, not bookkeeping intent.
Non-dollar currency symbols are flagged but the dollar sign is not
type_mismatch warningThe classifier strips only a leading $ (with an optional sign) before testing, because that's the symbol it normalises. £, €, ¥, and inline codes like GBP are not stripped, so £125.00 is flagged as type_mismatch in a numeric column while $125.00 passes. For non-USD ledgers, strip the symbol in source with csv-find-replace so the amount column is bare decimals before import — most accounting importers expect that anyway.
EU comma-decimal (`12,50`) silently becomes the wrong number
type_mismatch warningThe classifier removes commas as thousands separators, so 12,50 becomes 1250 internally and 1.234,56 becomes 1.23456, which fails the decimal test and is flagged. This is the right outcome — an EU-formatted amount column will not import correctly into a US/UK-locale accounting tool. Convert to point-decimals before import; do not assume a flagged 12,50 is fine, because the imported value would be off by two orders of magnitude.
Mixed date formats in one column produce a `mixed` profile
Review — mixed profileIf a Date column contains both 2025-01-05 and 05/01/2025, neither format reaches the 85% dominance threshold, so the column is profiled as mixed. A mixed date column won't generate per-cell type_mismatch warnings (the mismatch check only runs when the dominant type is a concrete number/boolean/date), so the mixed badge in the column profile is itself your signal. Normalise to one format — ISO YYYY-MM-DD avoids day/month ambiguity — and re-validate.
Reconciliation, balances, and locked periods are not checked
Out of scopeThis is a structural validator, not an accounting engine. It does not verify that debits equal credits, that a running balance is consistent, or that a transaction date falls in an open period. Those are business rules QuickBooks or Xero enforce during import. Use this report to guarantee the file is clean and well-shaped, then let your accounting tool do the bookkeeping validation.
A required column is missing entirely
Not detectedThe validator profiles only the columns present. If your export lacks an Amount or Date column, the validator can't flag the absence — there's no accounting schema to compare against. Check your file's header row against your importer's required-field mapping (QuickBooks and Xero both walk you through column mapping at import). The validator confirms the columns you have are clean; you confirm you have the right columns.
A full-year export exceeds the free 500-row limit
Blocked on free tierFree tier caps at 2 MB and 500 rows. A single month of bank activity usually fits, but a 12-month export will not. The runner blocks the file before validating and shows the limit message. Either upgrade to Pro (100 MB / 100,000 rows) or split the export by month with csv-row-splitter and validate each chunk — which also mirrors how many accounting tools cap their own per-import row counts.
Empty `Amount` cells in a mostly-filled column are flagged
empty_cell warningIf your Amount column is at least 85% populated and a few cells are blank, those blanks are flagged as empty_cell warnings — typically a transaction whose amount didn't export. Verify each one against the source statement; a blank amount usually means a parsing or export error. If a column is sparsely populated by design (e.g. an optional Memo), it exceeds the 15% empty threshold and stays quiet.
Only the first 250 issues are reported
By designThe reported issue list is capped at 250 to keep the browser responsive, and the Issues tile reflects that cap. A file with a systemic problem — every amount carrying a £, or a wrong delimiter — easily exceeds 250 issues. Fix the single root cause and re-validate; the count collapses. The downloaded JSON report contains the same capped list, so for very large issue counts, fix-and-re-run is the workflow, not scrolling 250 entries.
Frequently asked questions
Can it tell me which rows have text in the Amount column?
Yes — that's its strongest use for accounting files. If most of the Amount (or Debit / Credit) column parses as numbers, the validator infers the column type as number and flags every cell that doesn't, with the exact offending value in the detail string. It correctly accepts accounting formats — $1,299.50, bracketed negatives (45.00), plain decimals — and flags the real problems: non-dollar currency symbols, placeholder text like N/A or pending, and EU comma-decimals.
Does it integrate directly with QuickBooks or Xero?
No, and it doesn't need to. The validator is a pre-flight check: you validate the CSV locally, fix what it flags, then upload the clean file through QuickBooks' or Xero's own import wizard. Keeping it separate is the point — your bank figures never touch a third-party server, and you control the field mapping in your accounting tool as usual.
Is my financial data uploaded anywhere?
No. The file is read with the browser's File.text() and parsed by PapaParse entirely client-side. Bank balances, payee names, invoice totals, and account references never reach a JAD server — the tool is marked 100% client-side. Only an anonymous usage counter is recorded for signed-in dashboard stats, never the file contents. For sensitive financial files, that on-device guarantee is the main reason to use a browser-local validator.
Will bracketed negatives like (125.00) be flagged as errors?
No. The number classifier converts the parenthesis form (125.00) to -125.00 before it tests whether the cell is numeric, so a column of bracketed accounting negatives is correctly read as number with no type_mismatch warnings. The same goes for a leading $ and thousands commas — $1,234.56 is accepted. The classifier is tuned to recognise common ledger formatting so it surfaces genuine problems, not house style.
What about European number formats like 1.234,56?
Those will be flagged, and that's correct. The classifier removes commas as thousands separators, so 1.234,56 becomes 1.23456 internally — which fails the decimal test and raises a type_mismatch. More importantly, that value would import incorrectly into a US/UK-locale accounting tool. Convert EU comma-decimals to point-decimals in source (find-replace the comma decimal separator) before import, then re-validate.
Can it catch a duplicate transaction I imported twice?
Yes, if the file has an identifier column. Columns named like Transaction ID, Invoice Number, Reference, or Ref — or any name containing the id token — are treated as unique keys, so a repeated value is flagged as duplicate_key with the row it first appeared on. It also flags whole-row duplicates. To actually remove the duplicate, use csv-deduplicator on that column, or first inspect them with csv-duplicate-finder.
My date column shows as 'mixed' — what does that mean?
It means the column contains more than one date format, so no single format reaches the 85% dominance threshold and the column is profiled as mixed rather than date. That usually happens when ISO dates (2025-01-05) and slash dates (05/01/2025) are both present. Accounting importers want one consistent format, so normalise the column — ISO YYYY-MM-DD is safest because it avoids day/month ambiguity — and re-validate to get a clean date profile.
Does it check that my debits and credits balance?
No. This is a structural and type validator, not an accounting engine. It verifies the file's shape, types, encoding, and duplicate keys — it does not perform reconciliation, balance checks, or period validation. Those are business rules your accounting software enforces at import. Think of the validator as the step that guarantees QuickBooks or Xero will actually accept the file, after which their own logic takes over.
What does the health score mean for an accounting file?
It's round((1 - reportedIssues / totalCells) * 100) — the percentage of cells that are clean. A high score is reassuring but not sufficient: a 98% score can still include one row_width error that misaligns a whole row of amounts and sinks the import. For financial files especially, read the errors panel and confirm errors is zero before importing; treat the score as context, not a verdict.
Can I keep the report as an audit record?
Yes. 'Download report' saves a <filename>.health-report.json with the full stats, per-column profile, every reported issue (up to 250) with row/column references, the health score, and a 10-row preview. For accounting workflows that's a useful artefact — store it alongside the imported file so you have a record of the file's structural state at import time. Note the issue list is capped at 250, matching the UI.
Does it read Excel .xlsx files directly?
No. The validator reads text formats only — .csv, .tsv, and .txt — via the browser's text decoder. An .xlsx workbook is a binary format and would produce garbage or a parse failure. Save your workbook to CSV first (CSV UTF-8 recommended so accented payee names survive), then validate the saved file. If you need to pull tabular data out of a spreadsheet workflow, do the export step in Excel or your accounting tool before validating.
How big a file can I check?
Free tier handles up to 2 MB and 500 rows — enough for roughly a month of bank lines. Pro raises that to 100 MB and 100,000 rows. If a full-year export exceeds the free limit, split it into monthly chunks with csv-row-splitter and validate each one; that also mirrors how some accounting importers cap their own per-upload row counts, so smaller chunks import more reliably anyway.
Privacy first
Processing runs locally in your browser with PapaParse. No file is uploaded — only metadata counters are saved for signed-in dashboard stats.