How to read a bank statement CSV

Bank CSV exports are hostile in a consistent set of ways, and knowing the list turns a confusing file into a five-minute job. Every one of the problems below is a formatting decision the bank made, not damage to the file:

• Preamble rows. Account name, sort code, date range and a blank line before the header. Every parser you point at the file will treat the first of those as the column names.
• Two amount columns, Debit and Credit, instead of one signed amount, or one signed amount where the sign convention is the opposite of what you expect.
• Ambiguous dates. 03/04/2026 is 3 April in the UK and 4 March in the US, and the file does not say which.
• A description column carrying five fields at once: merchant, location, card number, reference, and the bank's own transaction type.
• Amounts as text, with thousands separators, currency symbols, or negatives written as (45.00).

Fix those five and the file is ordinary data.

Get the shape right first

Open the file and look at the first ten lines before anything else:

head -n 12 statement.csv

Count the preamble rows and note which line is the real header. Then skip them:

tail -n +5 statement.csv > clean.csv

If you are opening it in a spreadsheet instead, delete those rows before doing anything else. Sorting or filtering with them still in place puts the account summary in the middle of your transactions, where it is hard to spot and easy to include in a total.

Dates, which are the trap

This is the error that produces wrong answers rather than obvious breakage. A US-format parser reading a UK statement silently converts 3 April to 4 March for every day of the month up to the twelfth, and leaves the rest alone. The result looks completely normal and every monthly total is wrong.

Two ways to settle it, in order of reliability:

Find a day above 12. Scan the date column for a value like 25/12/2026. If the first component exceeds twelve, the format is day-first. This takes ten seconds and is definitive.

Check against a transaction you remember. Anything you can date from memory resolves the convention immediately.

Then convert explicitly rather than letting anything guess:

duckdb -c "SELECT strptime(\"Transaction Date\", '%d/%m/%Y') d, * FROM read_csv('clean.csv', all_varchar=true)"

all_varchar=true is the important half: it stops the reader inferring types at all, so you decide every conversion. That also protects the reference numbers and account digits that would otherwise arrive as floats.

Normalise the amount

You want one signed numeric column, and there are three shapes to convert from.

Separate Debit and Credit columns. Combine, with debits negative:

COALESCE(-TRY_CAST(replace("Debit", ',', '') AS DOUBLE), TRY_CAST(replace("Credit", ',', '') AS DOUBLE), 0)

Text with separators or symbols. Strip everything that is not a digit, a minus or a decimal point before casting. £1,234.56 and 1 234,56 both need this, and the second also needs the decimal comma handled.

Accounting negatives. (45.00) means −45.00. Nothing casts this correctly by default; replace the brackets first.

Then verify the conversion before trusting any total: the number of non-null amounts must equal the number of transaction rows. A TRY_CAST that failed gives you a silent NULL, and a NULL in a SUM is simply absent, which is how a total ends up plausibly wrong rather than obviously broken.

Split the description column

The description is where the useful information is hiding, and it arrives as one string:

CARD PAYMENT TO TESCO STORES 3421 ON 02-04-2026 GBP 43.19

You rarely need a full parser. Two operations cover most of what people want:

A merchant column, by matching against a list of names you care about. Twenty CASE WHEN description LIKE '%TESCO%' THEN 'Tesco' clauses will categorise the large majority of a personal statement, and the long tail is not worth automating.

A transaction type column, from the prefix. CARD PAYMENT, DIRECT DEBIT, FASTER PAYMENT, ATM. Banks are consistent about these within a single export, and grouping by type is often more informative than grouping by merchant.

What not to attempt: extracting a reliable amount or date from inside the description. Both are already columns, and the copies embedded in the text are formatted differently and sometimes disagree.

Then ask the actual question

Once the file has a date, a signed amount and a merchant, the questions are one line each:

SELECT strftime(d, '%Y-%m') month, sum(amount) FROM tx GROUP BY month ORDER BY month
SELECT merchant, count(*) n, sum(amount) FROM tx WHERE amount < 0 GROUP BY merchant ORDER BY sum(amount) LIMIT 20
SELECT * FROM tx WHERE amount < -100 ORDER BY amount

Monthly net, biggest outgoings by merchant, everything over a hundred. Those three answer most of what anyone opens a statement to find out.

CSV File Viewer - Smart CSViPhone & iPad · Android

SQL and the visual filter editor both run against the file where it sits, which is the point when the file is a bank export you would rather not upload anywhere. Charts come from the same query, so a monthly total becomes a bar chart in the same step.

What it does

That last consideration is worth stating plainly. A bank statement is among the most sensitive files most people handle: every merchant, every location, every regular payment, and often a partial account number. Free online CSV viewers and "convert your statement" sites upload the whole thing to a server you know nothing about. For this file specifically, a tool that works locally is not fussiness; it is the obvious default.

Fixing it up for someone else

Often the reason you opened the statement is that someone needs part of it: an accountant, a landlord, an expense claim. The work is subtraction:

1. Keep the columns that matter: date, description, amount. Delete the rest, including the running balance and the account digits.
2. Filter to the period or the category actually requested.
3. Add a category column if there is one they will ask for.
4. Export, and check the total on the export matches the total on your filter.

Redacting the balance column is worth doing deliberately rather than leaving it in out of laziness, because it discloses considerably more than the transactions do.

CSV Editor - Smart CSViPhone & iPad

Deleting columns, filtering to a period and adding a computed category column are all direct edits here, and formula columns save their results into the CSV, so the file you hand over is a plain CSV that opens correctly in whatever the recipient uses.

What it does

Multiple months, multiple accounts

Banks export one period at a time, so any question spanning a year involves several files. Two things to do before combining them:

Check the headers match. Banks change export formats between years, and a column that moved position will misalign everything after it if you concatenate blindly. Compare the header lines first; the guide to merging CSV files covers the column-name-based merge that survives this.

Add an account column before combining files from different accounts. Without it, a transfer between your own accounts appears twice, once as an outgoing and once as an incoming, and every total that ignores that is wrong by twice the transfer amount. Tagging the source, then excluding internal transfers explicitly, is the only way to get a household total that means anything.