I run ScanCompte, a small web app that turns an exported bank statement into a readable summary of where the money goes. Users upload a CSV from their bank. That sounds like the easy part, but it turned out to be the hardest.

Before anyone mentions it: standard formats do exist (OFX, QIF, CAMT.053). But the "Export to CSV" button most people get in their online banking follows no standard. Every bank invents its own file, and sometimes one bank produces two different files depending on the account type.

Here's what "no standard" looks like with French banks.

1. Encoding: a coin toss

Some exports are UTF-8, many are Windows-1252, and some UTF-8 files start with a BOM that ends up glued to the first column name. So your code looks for Date and finds \xEF\xBB\xBFDate.

When the encoding is wrong, "Prélèvement" becomes "Prélèvement", and any matching on labels fails without an error.

2. The separator: usually not a comma

French numbers use a decimal comma (1 234,56), so most banks separate columns with a semicolon. Most, not all. Don't assume the separator; detect it from the header line.

A minimal reader covering points 1 and 2:

function readBankCsv(string $path): array { $raw = file_get_contents($path); $raw = preg_replace('/^\xEF\xBB\xBF/', '', $raw); // strip BOM if (!mb_check_encoding($raw, 'UTF-8')) { $raw = mb_convert_encoding($raw, 'UTF-8', 'Windows-1252'); } $firstLine = strtok($raw, "\n"); $delimiter = substr_count($firstLine, ';') >= substr_count($firstLine, ',') ? ';' : ','; $stream = fopen('php://temp', 'r+'); fwrite($stream, $raw); rewind($stream); $rows = []; while (($row = fgetcsv($stream, 0, $delimiter, '"', '')) !== false) { $rows[] = $row; } fclose($stream); return $rows; }

Use fgetcsv on a stream rather than splitting on newlines, because a quoted label can contain a line break. Pass the escape argument explicitly: relying on its default is deprecated as of PHP 8.4.

3. No common template

This is the real problem. Once the file reads correctly, you still have to map it to one shape: date, label, signed amount. Each bank has its own:

  • Headers: Date opération, Date de valeur, Date, Libellé, Libelle, Détail, Montant, Débit, Crédit...
  • Amounts: one signed column, or separate debit and credit columns where debits are sometimes positive. Thousands separators can be a space, a non-breaking space, or a dot.
  • Dates: DD/MM/YYYY most of the time, but not always.
  • Preamble: some files put account details above the real header row, so it isn't on line 1.

The approach that holds up is boring: find the header row by matching known column names, map columns through an alias list, and convert every amount to a signed integer number of cents before doing any maths.

4. Order and length aren't standard either

Some banks export newest first, others oldest first. Length varies too: one user sends a month, another sends six years.

My first version refused files over 100 lines. That was the worst step in the whole funnel. Nobody counts the lines of a CSV before exporting it, and the rejection came after they had done the work of exporting. Now the app keeps the header plus the first 300 transactions (about three months, enough for a monthly charge to show up three times) and tells the user before upload which part will be analysed. It counts the lines in the browser as soon as the file is picked.

5. The export button is the first hurdle

The CSV also has to exist. Some banks hide the export three menus deep, some only offer it on desktop, and some only offer PDF. Users get stuck before they ever see my app, so I ended up writing one export guide per bank.

If you're building something similar

Collect real exports from as many banks as you can (anonymized), and test every change against all of them. Each bank has its own quirks, and real files catch more than reasoning about the format ever will.

I'm curious: which file format has caused you the most trouble?