Importing bank statements in the browser: OFX, messy CSVs and duplicate detection

Leonardo Marcus — Wed, 04 Nov 2026 16:53:58 +0000

When I built FinControl, a personal finance app in Next.js + Supabase, the feature that took the longest wasn't the dashboard or the Stripe billing. It was "Import bank statement".

It sounds simple: the user uploads a file, you create transactions. In practice, every bank exports something slightly different. Here's what I learned, with the actual code (TypeScript, no libraries).

1. Decode the file before you parse it

The first bug report you'll get is "the descriptions have weird characters". Many banks still export in Windows-1252, not UTF-8. The fix is to try UTF-8 in strict mode and fall back:

export function decodeStatement(buffer: ArrayBuffer) {
  try {
    // fatal: true makes invalid UTF-8 throw instead of producing "�"
    const text = new TextDecoder("utf-8", { fatal: true }).decode(buffer);
    return text.charCodeAt(0) === 0xfeff ? text.slice(1) : text; // drop the BOM
  } catch {
    return new TextDecoder("windows-1252").decode(buffer);
  }
}

Everything runs in the browser, so the file never touches the server until the user confirms the import.

2. OFX is easier than it looks

OFX comes in two flavours: 1.x (SGML, no closing tags) and 2.x (XML). Instead of pulling in an XML parser that chokes on 1.x, I split on <STMTTRN> and read each tag with a regex that stops at the next < or line break. That one approach handles both versions:

function readTag(block: string, tag: string) {
  const match = block.match(new RegExp(`<${tag}>([^<\\r\\n]*)`, "i"));
  return match ? match[1].trim() : "";
}

export function parseOfx(text: string) {
  const rows = [];
  const blocks = text.split(/<STMTTRN>/i).slice(1);
  for (const raw of blocks) {
    const block = raw.split(/<\/STMTTRN>/i)[0];
    const date = parseDate(readTag(block, "DTPOSTED"), true); // 20261023120000[-3:BRT]
    const amount = parseAmount(readTag(block, "TRNAMT"));
    const description = readTag(block, "MEMO") || readTag(block, "NAME");
    if (!date || !Number.isFinite(amount) || amount === 0) continue;
    rows.push({ date, description, amount: Math.abs(amount), type: amount < 0 ? "expense" : "income" });
  }
  return rows;
}

Some banks put the useful text in NAME, others in MEMO, some repeat it in both. I combine them when they differ and keep the longer one when one contains the other.

3. Amounts: the "1.234" problem

CSV exports are where it gets fun. The same column can contain 1.234,56 (Brazil, much of Europe), 1,234.56 (US), -45,90, (45.90), 45,90- or 10,00 D.

The rule that works: the last separator is the decimal one, unless it's clearly a thousands group.

const lastComma = text.lastIndexOf(",");
const lastDot = text.lastIndexOf(".");
if (lastComma > -1 && lastDot > -1) {
  const decimal = lastComma > lastDot ? "," : ".";
  const thousands = decimal === "," ? "." : ",";
  text = text.split(thousands).join("").replace(decimal, ".");
}

When there's only one separator, 1.234 is ambiguous. I treat a dot followed by exactly three digits (and 1–3 digits before it) as thousands; everything else is a decimal. Negative signs come from a leading -, a trailing -, parentheses or a D (debit) suffix.

4. Dates: look at the whole file, not one row

03/04/2026 is March 4th in the US and April 3rd almost everywhere else. One row can't tell you which. The whole file usually can:

export function detectDayFirst(samples: string[], fallback: boolean) {
  let dayFirst = false, monthFirst = false;
  for (const s of samples) {
    const p = splitDate(s);            // { a, b, year } for "a/b/year"
    if (!p || "iso" in p) continue;
    if (p.a > 12) dayFirst = true;     // "23/10/2026" can only be day-first
    if (p.b > 12) monthFirst = true;   // "10/23/2026" can only be month-first
  }
  if (dayFirst && !monthFirst) return true;
  if (monthFirst && !dayFirst) return false;
  return fallback;                     // the user's locale
}

If any date in the file has a first number above 12, the file is day-first. If you can't tell, fall back to the user's language setting.

5. Let the user map the columns (but guess first)

CSV headers are chaos: Data, Date, Data Lançamento, Histórico, Description, Valor, Amount... I keep a small list of hints per field and pre-select the most likely column. The user sees an editable preview before anything is saved, so a wrong guess costs one click instead of 40 bad transactions.

6. Duplicate detection that doesn't lie

People import overlapping statements all the time (October, then October again plus November). A naive "skip if the same transaction exists" breaks on a real case: two identical $5 coffees on the same day.

So the key is date + type + amount in cents + normalized description, and I count occurrences:

export function statementKey(row: Row) {
  return `${row.date}|${row.type}|${Math.round(row.amount * 100)}|${normalize(row.description)}`;
}

export function findDuplicates(rows: Row[], existing: Row[]) {
  const remaining = new Map<string, number>();
  for (const item of existing) {
    const key = statementKey(item);
    remaining.set(key, (remaining.get(key) || 0) + 1);
  }
  return rows.map((row) => {
    const key = statementKey(row);
    const left = remaining.get(key) || 0;
    if (left > 0) { remaining.set(key, left - 1); return true; } // already in the database
    return false;                                                  // genuinely new
  });
}

If the database has one coffee and the file has two, only the first is marked as a duplicate. Duplicates start unchecked in the preview, but the user can still include them.

7. Categories without AI

After parsing, each row gets a category suggestion from simple keyword rules the user controls ("UBER" → Transport, "IFOOD" → Food). It's fast, predictable and free. The user reviews everything in the preview, and only then does the app write to Supabase, where row-level security guarantees each user only ever touches their own rows.

Takeaways

Decode before you parse, and do it in the browser.
For OFX, a forgiving regex beats a strict XML parser.
Decide amount and date formats from the whole file, not one line.
Count duplicates instead of just checking existence.
Always show an editable preview before saving.

The full app (statement import, budgets, credit cards, goals, reports, an AI assistant, Stripe subscriptions, English and Portuguese) is available as a source kit: FinControl Source Kit. Live demo: fin-control-flame.vercel.app/en.

Happy to answer questions about any of this in the comments.

DEV Community: Leonardo Marcus