How to clean a UK bank CSV and lay it out for import in Google Sheets™

A step-by-step run through the add-on, from install to a converted tab. Scanning, every explanation, the ambiguity report, the space fix and Undo are free and unlimited. The other fixes, saved settings and the exportable report are paid, and each is marked Pro where it appears.

What the problem is

A bank export arrives as text: dates Sheets cannot sort, amounts with £ signs and commas, paid in and paid out in separate columns, a date and time in one cell, a total row nobody asked for. And a file built for upload fails in Xero™, QuickBooks™ or Sage™ for a heading it does not recognise, a date that reads two ways, or a zero in the wrong place.

This add-on reads the bank export, explains every finding in plain English — what is wrong, and why it matters to the import — and can lay the result out as a new tab in the package's layout. It edits the spreadsheet only and never connects to a bank or an accounting package.

Packages it lays a file out for

• Xero: the bank statement import layout.
• QuickBooks Online (UK): the 3-column form (Date, Description, Amount) and the 4-column form (Date, Description, Credit, Debit).
• Sage Accounting: the 3-column and 4-column bank import templates.

Before you start

• Google Sheets in a desktop browser. The add-on does not run in the Google Sheets mobile apps.
• The add-on installed — see Step 1.
• A spreadsheet with at least one sheet of bank data on it. Import tip: bring a bank CSV in with File › Import › Upload and untick “Convert text to numbers, dates and formulas”. Left ticked, Sheets parses dates using the spreadsheet's locale — a US-locale sheet silently turns a UK 06/03/2026 into 3 June — and turns text amounts into numbers before the scan can see what was there.
• Nothing leaves Google. The rules run in the sidebar, in your browser, against the Sheets API. The add-on's only network traffic is to the licence server, and only ever a licence key, an anonymous install id and which host this is. The privacy notice says so in full.

Step 1 — Install

Install it from Google Workspace™ Marketplace, or Extensions › Add-ons › Get add-ons and search for “UK Bank CSV Cleaner”.

Step 2 — Open the sidebar

In the spreadsheet, open Extensions › UK Bank CSV Cleaner › Open. A sidebar opens on the right.

Step 3 — Pick a direction and scan

Press Clean a bank CSV or Check before uploading. The direction is asked rather than guessed. To clean, choose a package under Lay it out for, or Just clean it, no conversion. To check, pick the package under Which package is this file for? or let it detect the package from the headings; the QuickBooks and Sage 3-column files share headings, so it asks which. The sidebar also says which bank it thinks the file came from — the file is never rewritten on a guess about the bank.

A sheet with nothing wrong says so, rather than showing an empty list.

Step 4 — Read the findings

Every finding names its rule code, what it found, and why it matters. The codes BK-01 to BK-20 are listed on the home page; the ones people meet first are:

Two notes are help text rather than findings: an unsigned amount with a debit/credit indicator (BK-11) and the report's formula-injection prefix (BK-18).

Step 5 — Convert to a new tab

Create the new tab on the BK-19 finding builds the package's layout in a new tab. The preview lists the exact rows and any warnings first. Your original sheet is never written: an unreadable date is left blank in the new tab and counted, never guessed.

Step 6 — Fix it Pro

Each fix shows a preview of the exact cell changes before anything is applied. The preview is built from the same plan the button executes, never from a second description of it.

• One-click fixes are safe unattended. The space fix (BK-20) is free.
• Guided fixes — dates, combining paid in and paid out — 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 the originals alone.

Step 7 — Undo, or keep the changes

Every fix first saves a hidden copy of the sheet before it changes anything. Undo in the sidebar restores that copy — free in every tier, always. After a conversion, Undo deletes the new tab. Keep changes deletes the hidden copies once you are happy with the result.

Step 8 — The exported report Pro

Export report writes the full finding list to its own tab in the spreadsheet, so it travels with the file and stays readable without the add-on open.

Step 9 — Entering a licence key

Expand Enter a licence key at the bottom of the sidebar, paste the key from the end of checkout, and press Activate. One key has 3 activations and takes effect immediately. Checkout is not open yet; see Prices.

Limits

• Desktop browser only. The add-on does not run in the Google Sheets mobile apps.
• Sheet size. Sheets up to 2,000,000 cells are supported. Larger sheets are declined with a message naming the size.
• Undo after a shape-changing fix restores row heights and hidden rows for the first 5,000 rows.
• Bank layouts. No bank's export layout has yet been confirmed against a real file from that bank, so today every rule that depends on a bank's layout explains and warns but never rewrites your cells, and the conversion to a new tab is held back with a message saying why. The fixes that act on what a cell itself says (text dates and amounts, whole-pound amounts, stray spaces) are not held back. Layouts switch on one bank at a time as real exports are confirmed.
• Package rules change. The package checks follow the published UK help pages of the packages as at the build date. Several behaviours could not yet be confirmed, so those rules explain and warn but never rewrite your cells. Banks also change their exports without notice.

FAQ

Does my data leave Google? No. The rules run in the sidebar, in your browser, against the Sheets API. The only network call this add-on makes is to the licence server, for a licence key, an anonymous install id and which host this is.

How should I import a bank CSV into Sheets? File › Import › Upload, with “Convert text to numbers, dates and formulas” unticked.

Does it work on a .xlsx file opened in Office editing mode? No — convert the file to a Google Sheet first (File › Save as Google Sheets). The add-on reads the Sheets API, which a file kept in Office editing mode does not provide.

Is this affiliated with any bank or accounting package? No. It is an independent add-on by AppGarden. The bank and package names are trademarks of their owners and are used only to describe compatible file formats.

Also for Excel™: UK Bank CSV Cleaner for Excel. The same licence key works in Google Sheets and Excel.

Support

Email support@appgarden.co.uk, and say which finding looked wrong. A spreadsheet that produced a wrong finding — or missed a real one — is the most useful thing you can send. Remove names and account numbers first, only send one you are content to share, and read the privacy notice.

Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. 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 Google.