Bank statements

Bank CSV import failed? Fixing columns and dates in UK exports

Why a UK-style bank CSV gets rejected by budgeting apps, and how to fix DD/MM/YYYY dates, split paid-out and paid-in columns and £ amounts step by step.

Editorial illustration for Bank CSV import failed? Fixing columns and dates in UK exports

If your budgeting app will not take a bank CSV, do not edit anything yet. Open the raw file in a plain text editor first, because what a spreadsheet shows you can hide the real problem. Two minutes of reading the actual characters usually tells you whether the trouble is the dates, the money columns or the shape of the file.

Why look at the file in a text editor first?

Spreadsheets are helpful in ways that cause trouble. Excel will quietly turn a date into its own idea of a date, drop leading zeros and reformat numbers, and if you save again you may have changed the file without noticing. A text editor shows the commas, quotation marks and line breaks exactly as the bank wrote them.

What you are looking for: how many lines sit above the column names, whether every row has the same count of commas, whether text fields are wrapped in quotation marks, and whether anything is written below the last transaction.

The three typical shapes of a UK-style export

ShapeWhat it looks likeWhat an importer may object to
Paid out and paid inTwo money columns, one of them blank on each rowIt wants a single signed amount
One signed amountSpending is negative, income positiveUsually fine, but check the sign direction
Amount plus a type columnAmount is always positive, with a Debit or Credit labelNeeds the label turned into a sign

Say a coffee shop line shows £3.80 in the Paid out column and nothing in Paid in. An importer that expects one amount column may read the blank as zero, or refuse the row. Merge the pair into one column and the problem disappears.

How do I merge paid-out and paid-in into one column?

  1. Insert a new column called Amount beside the two money columns.
  2. If Paid out is in column D and Paid in is in column E, enter =E2-D2 in the first data row of the new column. Blanks count as zero in this subtraction.
  3. Fill the formula down to the last row.
  4. Spot check: a £3.80 payment should show -3.80 and a £1,200.00 salary credit should show 1200.
  5. Copy the new column, paste as values only, then delete the old two columns before saving as CSV.

Dates: is DD/MM/YYYY the problem?

A day-first date such as 03/09/2026 means 3 September to you, but an importer built for month-first reads 9 March. Because the two are both valid, the app may not complain at all, which is worse: your spending lands in the wrong month. Always compare the month totals against your statement after an import.

To remove the ambiguity, write the date as 2026-09-03 before you save. If the dates are text, in cell A2 try =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)), which takes the day from the start, the month from the middle and the year from the end. Format the answer as YYYY-MM-DD and paste as values.

The pound sign and thousands separators

A cell reading £1,200.00 is text to many importers. Strip the £ and the comma with find and replace, then check that the column is formatted as Number. If a few values still refuse to turn into numbers, there may be invisible spaces; =TRIM(D2) followed by =VALUE() on the result clears most of them.

When the first try still fails

  • Test with five rows. If five import, add fifty, then the rest, so you find the bad row quickly.
  • Look for rows where a description contains a comma and is not wrapped in quotation marks. That pushes the amount one column to the right.
  • Check that every row has the same number of filled columns.
  • Keep the original download unchanged and work on a copy.

SpendixAI reads Excel and CSV exports and understands merchant, type, status and currency columns, and it shows rows for review before anything is saved. It is built around India and INR, so this is relevant only if you also have Indian accounts to track; even there, no export layout is promised to read perfectly, so the clean-up habits above still apply.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports