CSV column mapping that survives a bank redesign

What CSV actually guarantees

It is worth being precise, because the gap between the guarantee and the assumption is exactly where imports break.

Guaranteed: fields are separated by a delimiter, and rows by line breaks. Fields containing the delimiter can be quoted. That is close to the whole of it.

Not guaranteed: which delimiter. Whether there is a header. What the header calls anything. The order of the columns. The number of columns. The date format. The decimal separator. The thousands separator. The character encoding. How negatives are expressed. Whether extra rows appear before or after the data.

Put that way, CSV sits much closer to a PDF on the self-description axis than its reputation suggests. It removes the need to infer where fields end — which is a genuine and useful step — and leaves every semantic question open.

What actually changes in a redesign

Bank export formats change more often than people expect, and almost never with an announcement, because the export is a small feature of a large product. The changes fall into a short list.

A column is inserted — a reference, a category, a counterparty identifier — and everything after it shifts. A column is removed and everything shifts the other way. Columns are reordered in a redesign. A header is renamed or translated. One column is split into two, or two are merged. The date format changes. The delimiter changes. A preamble appears above the header.

Every one of those produces a file that is still valid CSV. That is the crux: there is no error to catch, because from the format's point of view nothing is wrong. A positional mapping applied to a shifted file happily reads descriptions as amounts and dates as references.

flowparse.io

Why positional mapping fails

Positional mapping — field three is the amount — is how most integrations start, because it is the fastest thing that works with the file in front of you. It carries an assumption that is never stated: that column order is a stable property of the source.

It is not stable, and worse, it is invisible when it changes. Nothing about a positional mapping can detect that the meaning of position three has changed, because position three is all it knows about.

It also fails on the ordinary variation between accounts at the same bank. Current accounts and card accounts frequently export different column sets, so a mapping built on one is wrong for the other without anything having been redesigned at all.

Position should be a last-resort fallback, never the primary signal — and when it is used as a fallback, it must be validated by something else before the data is accepted.

flowparse.io

Mapping by header name

Matching on header names is a large improvement and an incomplete solution. It survives reordering entirely, which is the most common change, and it makes the mapping readable to whoever maintains it.

Do it tolerantly. Normalise before comparing: trim whitespace, fold case, strip punctuation, collapse internal spaces, and remove any byte-order mark from the first header. Then match against a set of aliases per logical field rather than a single expected string — a transaction date might arrive as a date, a booking date, a posting date, a value date, or a translation of any of them.

Be careful with fuzzy matching, though. A field called something like a value date and one called a booking date are similar strings and different concepts, and a matcher tuned loosely enough to catch translations is loose enough to confuse those two. Prefer an explicit alias list you extend when you meet a new one.

And handle the no-header case: some exports have none at all. That is where positional fallback earns its keep, and where content detection becomes essential rather than merely advisable.

Mapping by content

The most robust signal is what the values look like, because it survives renaming, reordering, translation and the absence of headers simultaneously.

A date column is one where nearly every value parses as a date under a single consistent convention. Test the whole column rather than the first row — a single value tells you nothing about ambiguity, and the column as a whole usually resolves it.

An amount column contains numeric values, typically with at most two decimal places, and usually with both positive and negative values present. A column of exclusively positive integers of uniform length is far more likely to be a reference number.

A balance column is numeric and, crucially, has a property no other column has: its successive differences should equal the amounts. That is a test rather than a heuristic, and it identifies the balance and amount columns together.

A description column is the one with high textual variety and low structure. It is identified largely by exclusion, which is fine, because it is also the field where being slightly wrong costs least.

flowparse.io

The three-layer strategy

None of the three signals is sufficient alone. Combined in the right order, they are robust.

Layer one: names. Match normalised header names against alias lists. This resolves most files immediately and produces a mapping a human can read.

Layer two: content. For anything unresolved — or as confirmation of everything — classify columns by what their values look like. This is what carries you through a rename or a missing header.

Layer three: validation. Test the resulting mapping against the data as a whole, using the balance column where one exists. This is the layer that turns a plausible mapping into a confirmed one, and it is the one most often skipped.

Position appears nowhere in that list except as a tiebreak between two equally good candidates. That is deliberate — it is the signal that looks most authoritative and carries the least information.

The balance column is a free oracle

If the export includes a running balance, you have something unusual and valuable: a way for the file to check your interpretation of itself.

For every row, the balance minus the previous row's balance should equal that row's amount. Run it down the whole file. If it holds everywhere, then simultaneously: you identified the amount column correctly, you identified the balance column correctly, you got the sign convention right, you parsed the numbers correctly, and the rows are in the right order.

Five confirmations from one check, and none of them depends on trusting a header or a position. It is the closest thing to a proof available in a format that declares nothing.

Where the file also carries opening and closing figures, the totals identity gives you a second, independent check: opening plus every amount should equal closing. The two have different blind spots — the row-to-row chain misses a row dropped entirely, since the chain closes over the gap, while the totals identity catches exactly that. Run both.

flowparse.io

Validate before you trust

Where no balance column exists, weaker checks still catch most problems, and they cost almost nothing to run.

Does every value in the date column parse, under one convention rather than several? Are the dates within the period the file claims to cover? Are they broadly ordered? Do amounts have at most two decimal places? Are both positive and negative values present, or does the file use separate columns? Is the field count identical on every row? Are descriptions non-empty on rows that have amounts?

Any single failure is a reason to stop and look, not to filter the offending rows out. Rows that fail validation are exactly the rows most likely to matter — a row with an unparseable date is often the one where a quoted field contained a delimiter and shifted everything after it.

And count. The number of data rows parsed should equal the number of data lines in the file, less any recognised furniture. A silent drop of a handful of rows is the failure that survives every check that looks only at the rows it did parse.

Failing loudly

The most consequential design decision in an import is what happens when something does not look right, and the tempting answer is almost always the wrong one.

Skipping rows that do not parse keeps the import running and produces a quietly incomplete result. Falling back to a default when a column cannot be identified produces a confidently wrong result. Both convert a problem you could have fixed in ten minutes into one you discover at year end.

Reject the import instead. An import that refuses to run because the format changed is a five-minute inconvenience; an import that ran and put descriptions in the amount column is a quarter of corrupted data. The asymmetry is enormous and it points one way.

Where a human is present, show the proposed mapping with sample values from each column before committing. People spot a misidentified column instantly when they can see the values under it — and that review takes seconds compared to what it prevents.

flowparse.io

Delimiters and decimals travel together

In locales where the comma is the decimal separator, using a comma as the field delimiter would be ambiguous, so those exports commonly use semicolons instead. Tab-separated files also appear, and occasionally a pipe.

Detect the delimiter rather than assuming it. The reliable method is to try each candidate on the first several lines and pick the one that produces a consistent field count greater than one. A file parsed with the wrong delimiter yields one enormous field per row, which is trivially detectable and yet routinely goes unnoticed because nothing errors.

Then detect the decimal convention from the values. A comma followed by exactly two digits at the end of a number is a decimal separator; a full stop used as a thousands grouping alongside it confirms the reading. Getting this wrong changes values by a factor of a thousand, which is at least the kind of error a balance check catches instantly.

Quoting is the related trap. A description containing the delimiter must be quoted, and a parser that splits on the delimiter without honouring quotes will shift every field after it on that row alone. Use a real CSV parser rather than splitting strings.

flowparse.io

Encoding, and the invisible first character

Encoding problems announce themselves as mangled accented characters, which is at least visible. The subtler one is the byte-order mark: an invisible marker some tools write at the very start of a file.

It attaches itself to the first header name. Your comparison against the expected first column then fails, and the two strings look completely identical when printed. It is a genuinely maddening ten minutes the first time, and a one-line fix once you know: strip it before parsing.

Beyond that, be prepared for exports that are not Unicode. Legacy systems still emit regional encodings, and the symptom is accented characters in payee names appearing as replacement characters or as pairs of unrelated symbols. Detect and convert rather than assuming.

Excel is not a safe stop along the way

A very common pipeline is: download the CSV, open it in Excel to have a look, save it, import it. That middle step can change the data, silently, before anything else has run.

Leading zeros can be stripped from reference numbers, because the value looks like a number. Long numeric strings such as account or card identifiers can be rendered in scientific notation and lose their trailing digits. Text that resembles a date can be converted into one under whatever convention the machine's locale specifies.

None of that is Excel misbehaving — it is doing what a spreadsheet is supposed to do, which is interpret values helpfully. The problem is that the file which reaches your import is then no longer the file the bank produced, and the difference is invisible because it happened before anyone looked.

If Excel must be involved, use its import path and specify text for identifier columns rather than opening the file directly. Better: parse first, and use Excel to inspect the result rather than the source. And always keep the original download unmodified.

Preambles, trailers and other furniture

Bank exports frequently include lines that are not data. An account name and number at the top, the period covered, a generated-on timestamp. At the bottom, a total row, a count, or a note.

Parsers that assume line one is the header break immediately on the first kind. Parsers that assume every remaining line is data import the total row as a transaction on the second — which inflates every figure and, being a genuine number in the amount column, looks perfectly reasonable.

Locate the header rather than assuming it. The useful signal is field count: preamble lines usually have fewer fields than the table, so the first line whose field count matches the majority count for the file is almost always the header. Trailer rows are usually identifiable the same way, or by having no date.

A total row is worth capturing rather than merely discarding, incidentally. It gives you another figure to check your sum against — furniture that turns out to be evidence.

flowparse.io

Sign conventions, of which there are several

A bank CSV can express direction in at least five ways: a single signed amount column; separate debit and credit columns; a positive amount with a direction flag; parentheses around negatives; or a trailing rather than leading minus.

Detect which applies from the data, and confirm it against the balance movement rather than configuring it once. The failure mode is uniform and severe: getting direction wrong makes every payment look like income, and produces a difference of exactly twice the affected amounts — the fingerprint described in why reconciliations fail.

Separate debit and credit columns need care beyond the sign. Most rows have one populated and one empty, so a naive reading that takes whichever is non-empty usually works — until a row populates both, which some banks do for adjustments. Decide deliberately what that means rather than letting the code decide by accident.

Detecting the change early

Since redesigns are unannounced, the goal is to find out on the first file rather than at the quarter end. This is cheap to build and disproportionately valuable.

Fingerprint every file on arrival: delimiter, field count, and the normalised list of header names. Store it per source. When a new file's fingerprint differs from the previous one, alert — before processing, not after.

That single habit converts an entire class of silent corruption into a notification. Most of the alerts will be benign, and the benign ones cost a minute to confirm. The one that is not benign is the one that would otherwise have cost a quarter.

Keep the original files too, unmodified. When something is discovered to be wrong months later, the only way to establish whether the error came from the bank or from your own processing is to re-parse the original — which turns an unresolvable argument into a test you can run.

flowparse.io

The checklist

Everything above, in the order you would actually implement it.

Key takeaways

A CSV declares where fields end and nothing else. Every semantic question — meaning, order, date format, decimal separator, sign convention — lives in a mapping, and a positional mapping is the most fragile possible expression of it because it cannot detect its own obsolescence.

Map by name with tolerant normalisation, confirm by content shape, and validate the whole thing against the balance column, which confirms the amount column, the sign convention, the number parsing and the row order in one pass. Fail loudly when validation does not hold rather than skipping rows or defaulting.

Then fingerprint each file so the next redesign announces itself, keep originals unmodified, and keep spreadsheets out of the pipeline — because a helpful interpretation applied before anyone looked is the hardest kind of corruption to trace.

Frequently asked questions

Related reading