Lay a UK bank CSV out for Xero, QuickBooks or Sage bank import
The bank gave you a CSV and your accounting package rejects it, or accepts it with the wrong dates and signs. Each package asks for its own columns, so a bank export usually has to be rebuilt before it uploads.
What each package asks for
- QuickBooks Online (UK), 3 columns: Date, Description, Amount.
- QuickBooks Online (UK), 4 columns: Date, Description, Credit, Debit.
- Sage Accounting, 3 columns: Date, Description, Amount, and 4 columns: Date, Description, Money in, Money out.
- Xero: the bank statement import layout.
The QuickBooks and Sage 3-column layouts share headings, so a scan cannot tell them apart. The add-on asks which package the file is for.
Check by hand
Copy the columns you need to a new tab, rename the headings to the package's, make the dates one format, and make sure each amount is a number. Check the package's own help for its size limits before you upload.
What the add-on does
Clean a bank CSV with a package chosen under Lay it out for builds the layout in a new tab (BK-19). 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 rather than guessed, and Undo deletes the new tab.
Check before uploading works the other way: for a sheet you built yourself, it checks the package's rules (BK-17): headings, dates, amounts, zeros, a weekday in the date, size limits and blanks. Where the help for a rule could not be confirmed, it explains the problem and leaves your cells alone.
Building the layout depends on the bank's layout being confirmed, and the guide says where that stands. Converting a bank CSV to the target layout is free, and so is Undo. The add-on edits the spreadsheet only and never connects to a bank or to an accounting package.
Check the file before you trust it
UK Bank CSV Cleaner scans a UK bank CSV, or a sheet you are about to upload, and explains every finding. The full scan, every explanation and Undo are free, and your data stays in your Google™ account. It edits the spreadsheet only and never connects to your bank or to an accounting package.
About UK Bank CSV Cleaner — not yet on the Google Workspace™ Marketplace.
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.