From CSV Chaos to Clarity: A Step-by-Step Guide

Open a bank CSV for the first time and it looks like a wall of shouting. All caps, random numbers, store codes, city names you do not recognise. It is enough to make you close the file and decide budgeting is not for you.

It is not you. The file is just raw. Let me walk you from that mess to a clean overview, one step at a time.

What a raw export really looks like

Here is a typical row straight out of a bank:

2024-03-14,POS DEBIT 8823 AMZN MKTP US*Z1A2B,-42.30

Three things are buried in there: a date, a merchant, and an amount. The problem is that the merchant is wearing a disguise. "AMZN MKTP US" is just Amazon with a payment code stapled to it. Every messy row is hiding something simple.

Step 1: Get everything into one place

Export a CSV from each account and card, then bring them together. Your spending does not live in one account, so your overview should not either. One checking account, one savings, three cards? Import all five files. This is the consolidated view that makes the numbers honest.

Step 2: Clean the transaction descriptions

This is where chaos becomes clarity. Cryptic codes get rewritten into names a human recognises. A few real examples:

The pattern is always the same: strip the payment codes, store numbers, and city names, and you get the actual merchant. Most banks use one of a few formats (POS DEBIT + code + merchant, or merchant + reference number), so once you learn yours, the cleanup becomes predictable.

You can do this by hand with find-and-replace, but it gets old fast with 200 rows. A keyword-based formula handles it automatically. I wrote up the exact formulas for auto-categorizing transactions in Google Sheets, including the SEARCH + IFS approach that matches keywords to categories. The same technique works for cleaning names.

Step 3: Tag each transaction into a category

With clean names in place, categories fall into line. Amazon goes to Shopping, Uber to Transport, YouTube Premium to YouTube Premium, FairPrice to Groceries. Now you are not staring at fifty transactions. You are looking at five or six categories that add up to your month.

The simplest approach is a keyword table: one column for the keyword ("GRAB", "FAIRPRICE", "YOUTUBE"), one column for the category it maps to. Then a formula checks each transaction description against the table and assigns the first match. Anything that does not match lands in "Uncategorized" for a quick manual fix.

I covered the full setup, including the formula and a sample keyword table, in my post on automating expense tracking in Google Sheets. If you want to see how the planned vs actual comparison works after categorization, that is in the planned vs actual spending post.

Because the rules are yours, you decide the edge cases. Is a big supermarket run "Groceries" or "Household"? Your call, and it stays consistent once you set it.

Step 4: Read the surplus

Here is the line the whole exercise exists for:

Income - Expenses - Savings = Surplus

That one number is the pulse of your month. Positive means breathing room. Negative means something needs a look, and now you can see exactly which category to look at, instead of guessing.

Step 5: Make it repeatable

The first time takes the longest, because you are setting up the cleanup rules. After that, each month is a quick loop: export, import, glance at the surplus. Fifteen minutes and the chaos is handled.

That is the whole trick. The CSV never stops being messy. You just stop being the one who has to untangle it by hand.