Parse and normalize bank CSV/Excel exports into the app's transaction data model. Use when building or modifying the import feature, adding support for new bank formats, or handling transaction deduplication.
Bank exports come in wildly different formats. This skill standardizes parsing, field mapping, deduplication, and auto-categorization.
Upload → Detect Format → Parse Rows → Normalize → Deduplicate → Auto-Categorize → Preview → Save
papaparse (CSV) or xlsx / sheetjs (Excel)Transaction shapeimport_hash and filter out existing transactionstransactions table| Task | Library | Why |
|---|---|---|
| CSV parsing | papaparse | Handles encoding, delimiters, quoted fields |
| Excel parsing | xlsx (SheetJS CE) | Reads .xlsx/.xls without server-side Office |
Before saving, every row must be mapped to:
interface ImportedRow {
date: Date;
description: string;
amount: number; // cents, positive = income, negative = expense
importHash: string; // SHA-256(date|amount|description)
categoryId?: number; // from auto-categorization, nullable
}
Each bank format is defined as a profile in src/lib/import/formats.ts:
interface BankFormat {
id: string; // e.g. "ing-be", "kbc-be"
name: string; // "ING Belgium"
dateColumn: string; // header name for date
dateFormat: string; // e.g. "DD/MM/YYYY", "YYYY-MM-DD"
descriptionColumns: string[]; // headers to concatenate for description
amountColumn?: string; // single signed amount column
debitColumn?: string; // separate debit column (always positive)
creditColumn?: string; // separate credit column (always positive)
delimiter?: string; // default ","
skipRows?: number; // header rows to skip
encoding?: string; // default "utf-8"
}
Amount handling: Banks use one of two conventions:
For known bank format mappings, see bank-formats.md.
Compute a hash for each row to prevent re-importing the same transaction:
import { createHash } from "crypto";
function computeImportHash(date: string, amount: number, description: string): string {
const raw = `${date}|${amount}|${description}`;
return createHash("sha256").update(raw).digest("hex");
}
Before inserting, query existing hashes for the target account:
const existing = await db
.select({ hash: transactions.importHash })
.from(transactions)
.where(eq(transactions.accountId, accountId));
const existingSet = new Set(existing.map((r) => r.hash));
const newRows = parsed.filter((r) => !existingSet.has(r.importHash));
Define rules as patterns matched against the transaction description:
interface CategoryRule {
categoryId: number;
patterns: RegExp[];
}
// Example rules
const rules: CategoryRule[] = [
{ categoryId: 1, patterns: [/colruyt/i, /delhaize/i, /aldi/i, /lidl/i, /carrefour/i] },
{ categoryId: 5, patterns: [/netflix/i, /spotify/i, /disney/i] },
{ categoryId: 3, patterns: [/q8/i, /total ?energies/i, /shell/i] },
];
categoryId: null (user categorizes manually)errors[] array for user reviewerrors[]const importRowSchema = z.object({
date: z.coerce.date(),
description: z.string().min(1),
amount: z.number().int(),
importHash: z.string().length(64),
categoryId: z.number().int().nullable().optional(),
});