Product guides

Mapping columns when a CSV looks unusual

Not every bank file arrives in a tidy shape. Learn how the mapping step works, how to choose between signed and split amounts, and how to check your choices.

Editorial illustration for Mapping columns when a CSV looks unusual

Most statement files import without fuss, but some arrive with columns the app cannot interpret by itself: odd headers, dates in unfamiliar formats, or amounts split in two ways. For those, the import flow includes a step to map columns. It is a small screen, but a few minutes of care there decide whether every row that follows is right or wrong.

What mapping is for

Mapping tells the app which column of your file holds which kind of information. At a minimum, it needs to know where the date is and where the amount is. The screen prompts you to pick a date column and an amount column so that it can show what it would read. Until you do, the preview stays empty.

The preview matters. It shows how rows will look after reading, and it is your chance to confirm before anything is created.

Signed amounts versus debit and credit columns

Banks present money in two broad styles, and the mapping screen supports both.

  • **Signed amount.** One column holds all values, with money out shown as negative or marked in some other way.
  • **Debit and credit.** Two columns, one for money leaving and one for money arriving. A given row usually has a value in only one of them.

Choose the style that matches your file. If you pick the signed style for a file with separate columns, one direction of money will be missed. If you pick debit and credit for a signed file, the app will find nothing in one of the columns.

Checking dates

Dates are the second hazard. A file may write the first of March as 01/03 or as 03/01, depending on convention. If the preview shows months that cannot exist, such as a thirteenth month, the format is wrong. If the preview shows plausible but swapped dates, check against a transaction you remember.

A good habit is to verify the first and last rows against the statement. If both are correct, the middle is almost certainly correct too.

Header rows, notes and totals

Files often contain extra lines: bank names at the top, opening balances, summary rows at the bottom. The preview helps spot these. If a summary row appears as a transaction in the preview, remove or exclude it during review so the total is not double counted.

When mapping will not save you

Some files are simply unreadable: images of statements, heavily formatted exports, or password-protected documents. The screen reports when no rows could be read. In that case, try a different download format from the bank, such as CSV instead of PDF, or open and re-save the file as a clean spreadsheet.

Symptom in previewLikely fix
No rows appearPick date and amount columns
Only one direction of moneySwitch between signed and debit-credit style
Impossible datesChange date format
Summary rows as transactionsExclude them in review
Amounts ten times too large or smallCheck decimal and thousand separators

Saving what works

After a successful mapping, jot down which columns and styles worked for each bank. A one-line note per bank is enough. Statement layouts change rarely, and the next time you import from the same source you will remember the choices instantly instead of rediscovering them by trial.

Practise on a small file

If you are unsure about a new bank's format, import a short period first, perhaps a week. Check the result, then repeat with the full year using the same settings. A small test costs minutes and avoids a large import that needs discarding.

Once a mapping works for a bank, note the settings. Next month's file from the same bank will look the same, and you will move through the step in seconds.

SPENDIXAI JOURNAL

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

Explore your reports