UK Bank CSV Cleaner
A UK bank CSV that Excel™ can use, and a bank file laid out for the bank import of Xero™, QuickBooks™ or Sage™. Free to check. Dates that read two ways are listed before anything is written, and the conversion goes to a new sheet, never over your original.
Install: Not yet on Microsoft AppSource: the listing is in preparation. It will be an Excel task-pane add-in for Excel™ on Windows, Mac and the web. This page is the support and documentation page the listing will point to. This page is the support and documentation page the listing will point to.
Before
Unusable
A bank export with dates stored as text, amounts with £ signs and thousands commas, paid in and paid out in two columns, and a row of spaces at the end.
After
Ready
Real dates, real numbers, one signed amount, and a new sheet in the layout your accounting package asks for. A preview first, and Undo is always there.
The one to know about
A bank import fails on small things: a date that reads 03/04 two ways, an amount that is text, a heading the package does not recognise. Each export also has its own shape. This add-in reads the shape, says what is wrong in plain English, and lays the result out as a new sheet, so the file you upload is not the file the bank gave you, and the file the bank gave you is untouched.
What it covers
UK bank statement CSVs, by spreadsheet only: the add-in never connects to a bank or to an accounting package.
- Clean a bank CSV: make the dates and amounts of an export usable in Excel, whichever UK bank it came from.
- Lay it out for: Xero bank statement import, QuickBooks Online (UK) 3-column or 4-column, Sage Accounting 3-column or 4-column. The result is written to a new sheet.
- Check before uploading: a sheet you built yourself, checked against the package's own bank-import rules.
Where a bank's layout has not been confirmed against a real export, the add-in says so and explains what it found rather than rewriting your cells. The current state is in the guide.
Three steps
- Install. Once the add-in is listed, from Microsoft AppSource, or Home › Add-ins › Get Add-ins in Excel and search for the name.
- Scan. Open a workbook, then choose UK Bank CSV Cleaner on the Home tab. Pick Clean a bank CSV or Check before uploading.
- Fix. Every finding explains what is wrong and why it matters to a bank import. The safe ones have a one-click or guided fix, with a preview of the exact cell changes before anything is applied — and every ambiguous date listed before the write, never after it.
What it checks
Every finding carries a rule code, so a report you read again in six months still says which check produced which line.
Cleaning an export
BK-01 | Bank not recognised, or only a weak match |
BK-02 | Rows below the data; opening-balance and other non-transaction rows |
BK-03 | Header row repeated inside the data |
BK-04 | Empty column, or a column with no heading |
BK-05 | Year-less and two-digit-year dates |
BK-06 | Date and time in one cell |
BK-07 | Dates stored as text, and which way round they read |
BK-08 | Whole-pound amounts missing their decimals |
BK-09 | Amounts stored as text (symbols, separators, brackets) |
BK-10 | Paid in and paid out in two columns |
BK-11 | Unsigned amount with a debit/credit indicator, reversed card signs (explained only) |
BK-12 | Payment type apart from the description; missing payee |
BK-13 | Foreign-currency rows |
BK-14 | Row count at a known export cap |
BK-15 | Duplicate rows from overlapping exports |
BK-16 | Running balance that does not follow the amounts |
BK-19 | Convert to the chosen package's layout, in a new sheet |
BK-20 | Stray spaces, including blank cells |
Checking before an upload
BK-17 | The package's layout: headings, dates, amounts, zeros, weekday, size limits, blanks |
BK-18 | Formula-injection prefix in the exported report (explained only) |
BK-20 | Stray spaces, including blank cells |
See the guide for what the rules mean. The package rules are built from the published help pages of the packages, and the bank layouts from the banks' own exports where we have seen one; where a behaviour could not be confirmed, the rule explains the problem and leaves your cells alone.
Free vs Pro
Free
£0
- Unlimited scans and explanations, both directions
- The ambiguity report
- The conversion to a new sheet, once the bank's layout is confirmed
- The space fix, applied
- Undo — always, in every tier
Pro
£25 per year, or £3 a month — coming soon
- Everything in Free
- Every other fix, one-click and guided, with a preview
- Saved settings, so the date question is asked once
- An exportable report, as a CSV or a summary
- 3 activations on one key
- 30-day free trial, no card · 30-day full refund
Your data
Your bank data never leaves your computer. The rules run in the task pane, on your machine. The only network traffic this add-in makes is to the licence server, and only a licence key, an anonymous install id and which host this is ever cross it. The add-in reads and writes this workbook only — not your other files. Privacy notice.
FAQ
Does my data leave my computer? No. Scanning and fixing both happen in the task pane, against the workbook, without your content going anywhere else.
Does it connect to my bank? No. It edits the spreadsheet only. You download the CSV from your bank and upload the result to your accounting package yourself.
How should I import a CSV into Excel? Data › From Text/CSV, then Transform Data and set the date and amount columns to Text, so they arrive as written and the scan can read them. Opening the CSV directly also works; Excel may then have turned dates into its own reading of them, which the scan reports.
What if I want to undo a fix? Every fix saves a restore point first. Press Undo in the task pane to go back, in any tier, at any time. Converting to a new sheet never changes the original sheet, and Undo deletes the new sheet.
Is it for UK banks only? Yes. Dates are read day-first and the package rules follow the UK help pages.
Is this affiliated with any bank or accounting package? No. It is an independent add-in by AppGarden. The names above are trademarks of their owners and are used only to describe compatible file formats.
Also for Google Sheets™: UK Bank CSV Cleaner for Google Sheets. The same licence key works in Excel™ and Google Sheets.
Support
Email support@appgarden.co.uk. Say which finding looked wrong and, if you can share it, a copy of the sheet with the problem (names and account numbers removed) — read the privacy notice first.
Microsoft and Excel are trademarks of the Microsoft group of companies. Not affiliated with, endorsed by or sponsored by Barclays, HSBC, Lloyds Banking Group, Halifax, Bank of Scotland, Starling Bank, Monzo, Xero Limited, Intuit Inc. or The Sage Group plc. Barclays, HSBC, Lloyds, Halifax, Bank of Scotland, Starling, Monzo, Xero, QuickBooks and Sage are trademarks of their respective owners and are used only to describe compatible file formats. UK Bank CSV Cleaner is not affiliated with or endorsed by Microsoft.