How to clean a QuickBooks™ export, and check a sheet before you upload it
A step-by-step run through the add-in in both directions. The whole check is free, both ways, and so is every explanation — and so is Undo.
Why a general cleaner is not enough
A generic data-cleaning add-in can tell you a column is text. It cannot tell you that a QuickBooks bank upload wants a zero amount left blank, that a weekday attached to a date will be refused, or that a heading must not include the word “Amount”. This one knows it is a QuickBooks file, checks the sheet against QuickBooks' import conventions, and explains every finding in plain English — what is wrong, and why it matters to the import. It edits the workbook only and never connects to QuickBooks.
Two directions, one pane:
- Out of QuickBooks. Export dates that will not work in Excel, numbers held as text that will not sum, bracketed negatives, and the trailing and non-breaking spaces every export carries.
- Into QuickBooks. A check before you upload: missing or misspelled columns, dates not in one readable format, a bank file whose amount is split across Credit and Debit, and zeros that QuickBooks wants blank.
Profiles
- Bank uploads: the 3-column form (Date, Description, Amount) and the 4-column form (Date, Description, Credit, Debit).
- Customers and suppliers: the contacts file (Full Name, Company Name and the rest).
- Journal entries: Journal No., Journal Date, Account Name, Description, Name, Debits, Credits.
- Report exports: a transaction report, cleaned for use in Excel.
- Invoices, chart of accounts and products and services imports have help text only, for now.
Before you start
- Excel on Windows, Mac, or the web. The pane is the same on all three.
- The add-in installed. See the support page for that.
- The export or upload file open in Excel, with the data on a sheet.
- Nothing leaves your machine. The add-in asks Excel for one permission, to read and change the open workbook, and talks to one server, only about your licence key. The privacy notice says so in full.
Step 1 — Open the pane and pick a direction
Open the file, then Home › QuickBooks import › Check sheet. The pane opens with the two directions and nothing else. Press Clean a QuickBooks export or Check before uploading. The same columns travel both ways, so the direction is asked rather than guessed, and it changes every check that runs. Nothing happens until you choose.
Step 2 — Check the file type
The pane says which kind of QuickBooks file it thinks this is, from its headers — change it there if it guessed wrong. A sheet with nothing wrong says so, rather than showing an empty list. There is no score: a sheet with findings never reads as clean.
Step 3 — Answer the date question
06/03/2026 is two real dates. Nothing inside a QuickBooks file states which one is
meant, so the pane asks once rather than guessing — which is the honest version of a guess.
On Pro the answer is remembered per import type.
Step 4 — Read a finding
Every finding is one card. It names its rule code, what it found, and why it matters to a QuickBooks import. The ambiguity is never a footnote — it is the screen: the card lists every value that could be read both ways, by row, before anything is written, then the whole change cell by cell.
- Blocker — the data is wrong, or the import will fail.
- Degrader — works now, will bite later.
- Advisory — worth knowing.
The range at the top right is a link — click it and Excel selects those cells. On the free tier the card ends with a line saying the fix is part of Pro, and that the scan, this explanation and Undo stay free. The preview table is shown either way, so you can make the change by hand from it.
Cleaning an export (QE)
QE-01 | Report title lines above the headers, and basis or date-stamp lines below |
QE-02 | Total, Subtotal and balance rows mixed in with the data |
QE-03 | Account name only on a group heading row |
QE-04 | Stray spaces, including non-breaking ones |
QE-05 | Dates stored as text, with the ambiguity report |
QE-06 | Amounts stored as text, and bracketed negatives |
QE-07 | Blank spacer columns between real ones |
QE-08 | Debit and Credit columns to one signed amount |
Checking before an upload (QI)
QI-01 | Zeros in Amount, Credit or Debit, which QuickBooks wants blank |
QI-02 | Credit and Debit headings that include the word “Amount” |
QI-03 | A day of the week attached to a date |
QI-04 | Dates that are not in one readable format, or that read two ways |
QI-05 | Files over the row and size limits (bank files: 1,000 lines and 350 KB, or 500 lines on the Sole Trader plan, per Intuit's help) |
QI-06 | Amount used with Credit or Debit, one-sided Credit and Debit, missing columns |
QI-07 | Currency symbols, separators and special characters in amounts and descriptions |
QI-08 | Customers and suppliers: names with a colon or quote, duplicates, headings, tab name, blanks |
QI-09 | Journals: balance per journal, blank cells, a Name missing on Creditors or Debtors lines |
QI-12 | Line breaks inside cells, and a semicolon-separated file that landed in one column |
QI-15 | Spaces in blank cells and around values |
Three further notes are help text rather than findings: the invoices, chart of accounts and products and services imports have no checks yet, and an account that does not exist in your QuickBooks company cannot be seen from a sheet.
Step 5 — Fix it Pro
On a paid licence the same card ends in a button. It says Apply, as previewed, because that is the guarantee: it writes exactly the table above it, and nothing else. Each fix shows a preview of the exact cell changes before anything is applied, built from the same plan the button executes.
- One-click fixes are safe unattended. The space fix (QE-04, QI-15) is free.
- Guided fixes — dates, weekdays, combining Debit and Credit — ask you to confirm before the write, with the ambiguity report open above the button for dates.
The remaining findings are explained but not applied. A value the parser cannot read is never rewritten, and a guess never overwrites its source — a combined signed amount is written into a new column, leaving Debit and Credit alone. The pane then re-reads the sheet and shows what is left.
The space fix is free. Trailing, leading and non-breaking spaces are applied on every tier: they are the defect most likely to be the actual cause of an import failure, and charging for a whitespace trim would be absurd.
Step 6 — Undo
Undo is free in every tier, always. It does not lapse with a licence and it keeps working after one is cancelled. The button names the fix it will reverse. Use it rather than Ctrl+Z — what Excel's own undo does after an add-in writes is not defined by Office and varies by platform.
Step 7 — Check before uploading
Press Check before uploading and the same sheet is judged against what QuickBooks will accept, rather than against what Excel can use. Run this before every upload, not after a failure. A rejected file tells you the first thing wrong with it; this tells you all of them at once.
Exporting what it found
Export CSV gives you a row per finding for a spreadsheet or a ticket, and Export summary gives you Markdown to paste into a message. Both export the check you are looking at, not a fresh one, so the file always agrees with the screen.
Entering a licence key
Open Licence at the bottom of the pane, paste the key from the end of checkout and press Activate. One key has 3 activations. See Prices.
When it will not help
- Detecting the date format. Nothing inside a QuickBooks file states it. The pane asks once, then reports every ambiguous value it acted on.
- The file on disk. It reads the active sheet, so a file's encoding is not visible to it. What it can see — a line break inside a cell, or text already mangled by being opened as Latin-1 — it reports.
- A date it cannot parse. Explained rather than guessed at. A wrong date posted to a ledger is worse than a date the tool declined to touch, so values it cannot read are never rewritten.
- QuickBooks' rules change. The checks follow QuickBooks' published UK help pages as at the build date. Several behaviours could not yet be confirmed against a real QuickBooks company, so those rules explain and warn but never rewrite your cells.
- Mac and iPad are untested. The manifest permits them and Microsoft lists them, but no verification run has happened on either. Please report anything odd.
Also for Google Sheets™: Import Cleaner for QuickBooks™ for Google Sheets. The same licence key works in Excel™ and Google Sheets.
If something looks wrong
Email support@appgarden.co.uk, and say which platform you are on and what the pane showed. If a check reported something you believe is wrong, the file itself is the most useful thing you can send — but only send one you are content to share, and read the privacy notice first.