Something went wrong. Try again.
Convert CSV banking statements to hledger journals
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504//! Row conversion (M1-CSV-016, 017, 020, 021).//!//! Turns the decoded cells of one data row into a typed date, amount and//! currency.//!//! Conversion reports rather than refuses. M1-CSV-022 requires a row with a//! date, amount or currency error to enter rule evaluation anyway, and//! M1-DIAG-004 wants independent errors all reported, so all three are//! attempted and each is optional in the result. Whether an incomplete row//! blocks generation is the engine's decision, not this module's.
const std = @import("std");const Decimal = @import("Decimal.zig");const diag = @import("diag.zig");const fields = @import("csv/fields.zig");const schema = @import("yaml/schema.zig");pub const Date = @import("Date.zig");pub const Currency = @import("currency.zig").Currency;
pub const Error = schema.Error;
/// M1-CSV-021: exactly these two./// M1-CSV-020: one value for every row, or one column read per row.pub const CurrencySource = union(enum) { column: usize, fixed: Currency,};
/// The profile's `transaction` section, resolved: which column holds the/// date, the amount and the currency, and how each is spelled. Named for/// what it maps, and to keep it distinct from a YAML `schema.Mapping`.pub const ColumnMapping = struct { amount_column: usize, amount_format: Decimal.Format, currency: CurrencySource, date_column: usize, date_format: Date.Format,
/// Converts one row. Every field is attempted even when an earlier one /// failed, so all independent errors are reported (M1-DIAG-004). pub fn convert( self: ColumnMapping, data: fields.DataRow, file: []const u8, diags: *diag.List, ) Error!Converted { return .{ .date = try self.convertDate(data, file, diags), .amount = try self.convertAmount(data, file, diags), .currency = try self.convertCurrency(data, file, diags), }; }
fn convertDate( self: ColumnMapping, data: fields.DataRow, file: []const u8, diags: *diag.List, ) Error!?Date { const cell = data.cells[self.date_column]; return Date.parse(cell, self.date_format) orelse { try diags.addRow(file, data.physical_row, "'{s}' is not a {s} date", .{ cell, self.date_format.pattern(), }); return null; }; }
fn convertAmount( self: ColumnMapping, data: fields.DataRow, file: []const u8, diags: *diag.List, ) Error!?Decimal { const cell = data.cells[self.amount_column]; return Decimal.parse(cell, self.amount_format) catch { try diags.addRow(file, data.physical_row, "'{s}' is not a valid amount", .{cell}); return null; }; }
fn convertCurrency( self: ColumnMapping, data: fields.DataRow, file: []const u8, diags: *diag.List, ) Error!?Currency { const cell = switch (self.currency) { .fixed => |currency| return currency, .column => |index| data.cells[index], }; return Currency.parse(cell) orelse { try diags.addRow(file, data.physical_row, "'{s}' is not a supported currency", .{cell}); return null; }; }
/// Decodes `profile.transaction`, resolving each field alias against the /// layout. The cursor already carries the file, arena and diagnostics. /// The amount format is the profile's, read once by `loadAmountFormat`. pub fn load( transaction: schema.Cursor, layout: fields.Layout, amount_format: Decimal.Format, ) Error!ColumnMapping { const date = try transaction.child("date"); const date_column = try resolveField(date, layout); const format = try (try date.child("format")).enumIndex(&Date.Format.patterns);
const amount_column = try resolveField(try transaction.child("amount"), layout);
return .{ .date_column = date_column, .date_format = Date.Format.all[format], .amount_column = amount_column, .amount_format = amount_format, .currency = try loadCurrency(try transaction.child("currency"), layout), }; }
/// M1-CSV-017. Numeric predicates read cells with it too (M1-RULE-016), /// so it does not depend on any one file's layout. pub fn loadAmountFormat(transaction: schema.Cursor) Error!Decimal.Format { const amount = try transaction.child("amount"); const decimal = try (try amount.child("decimalSeparator")).enumTag(Decimal.Separator); const grouping = (try amount.optionalChild("groupSeparator")) orelse return .{ .decimal = decimal };
const group: ?Decimal.Separator = switch (try grouping.enumTag(Grouping)) { .comma => .comma, .dot => .dot, .none => null, }; // A cell like 1.234 would otherwise mean two different amounts. if (group == decimal) { return grouping.fail("'{s}' is already the decimal separator", .{@tagName(decimal)}); } return .{ .decimal = decimal, .group = group }; }
/// The spellings of `groupSeparator`; `none` is the default. const Grouping = enum { comma, dot, none };
fn loadCurrency(currency: schema.Cursor, layout: fields.Layout) Error!CurrencySource { if (try currency.optionalChild("fixed")) |fixed| { const text = try fixed.string(); const parsed = Currency.parse(text) orelse return fixed.fail("'{s}' is not a supported currency", .{text}); return .{ .fixed = parsed }; } return .{ .column = try resolveAlias(try currency.child("field"), layout) }; }
fn resolveField(parent: schema.Cursor, layout: fields.Layout) Error!usize { return resolveAlias(try parent.child("field"), layout); }
/// The alias must be one the profile declared (M1-YAML-012). fn resolveAlias(cursor: schema.Cursor, layout: fields.Layout) Error!usize { const alias = try cursor.string(); return layout.indexOf(alias) orelse cursor.fail("'{s}' is not a declared field alias", .{alias}); }
/// Test support: conversion is always read together with what it had to /// say about the row, so the tests ask for both at once. fn convertWith( self: ColumnMapping, arena: *std.heap.ArenaAllocator, cells: []const []const u8, ) !struct { converted: Converted, diags: *diag.List } { const alloc = arena.allocator(); const diags = try diag.List.create(alloc); const converted = try self.convert(rowOf(cells), "bank.csv", diags); return .{ .converted = converted, .diags = diags }; }};
pub const Converted = struct { amount: ?Decimal, currency: ?Currency, date: ?Date,
pub fn isComplete(self: Converted) bool { return self.date != null and self.amount != null and self.currency != null; }};
// -- tests -------------------------------------------------------------------
const expect = std.testing.expect;const expectEqual = std.testing.expectEqual;const expectEqualStrings = std.testing.expectEqualStrings;const expectFmt = std.testing.expectFmt;const expectError = std.testing.expectError;
fn rowOf(cells: []const []const u8) fields.DataRow { return .{ .physical_row = 7, .cells = cells };}
const iso_eur: ColumnMapping = .{ .date_column = 0, .date_format = .iso, .amount_column = 1, .amount_format = .dot, .currency = .{ .fixed = .eur },};
test "M1-CSV-016: the ISO format parses and renders unchanged" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); const result = try iso_eur.convertWith(&arena, &.{ "2026-09-11", "1.50" }); const date = result.converted.date.?; try expectEqual(@as(u16, 2026), date.year); try expectEqual(@as(u8, 9), date.month); try expectEqual(@as(u8, 11), date.day);
try expectFmt("2026-09-11", "{f}", .{date});}
test "M1-CSV-016: the dotted format still renders as YYYY-MM-DD" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); var mapping = iso_eur; mapping.date_format = .dotted; const result = try mapping.convertWith(&arena, &.{ "11.09.2026", "1.50" });
try expectFmt("2026-09-11", "{f}", .{result.converted.date.?});}
test "M1-CSV-016: a two-digit year is a year from 2000 to 2099" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); var mapping = iso_eur; mapping.date_format = .dotted_short; for ([_][2][]const u8{ .{ "30.12.25", "2025-12-30" }, .{ "01.01.00", "2000-01-01" }, .{ "31.12.99", "2099-12-31" }, }) |case| { const result = try mapping.convertWith(&arena, &.{ case[0], "1.50" }); try expectFmt(case[1], "{f}", .{result.converted.date.?}); }}
test "M1-CSV-016: the two-digit-year format is exact as well" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); var short = iso_eur; short.date_format = .dotted_short; for ([_][]const u8{ "30.12.2025", // the four-digit format "1.2.25", // unpadded "30-12-25", // wrong separator "30.12.2x", "31.04.25", // no such day "29.02.25", // 2025 is no leap year }) |bad| { const result = try short.convertWith(&arena, &.{ bad, "1.50" }); try expect(result.converted.date == null); } try expect((try short.convertWith(&arena, &.{ "29.02.24", "1" })).converted.date != null);
// The four-digit format does not accept two digits either. var dotted = iso_eur; dotted.date_format = .dotted; try expect((try dotted.convertWith(&arena, &.{ "30.12.25", "1" })).converted.date == null);}
test "M1-CSV-016: the cell must use the configured format exactly" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); for ([_][]const u8{ "2026-9-3", // unpadded "26-09-11", // short year "2026/09/11", // wrong separator "11.09.2026", // the other configured format " 2026-09-11", // surrounding space "2026-09-11 ", "2026-09-1x", "", }) |bad| { const result = try iso_eur.convertWith(&arena, &.{ bad, "1.50" }); try expect(result.converted.date == null); }}
test "M1-CSV-016: the date must be a real Gregorian date" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); for ([_][]const u8{ "2026-02-30", "2026-13-01", "2026-00-10", "2026-01-00", "2026-04-31" }) |bad| { const result = try iso_eur.convertWith(&arena, &.{ bad, "1.50" }); try expect(result.converted.date == null); }}
test "M1-CSV-016: leap years follow the Gregorian rule" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); try expect((try iso_eur.convertWith(&arena, &.{ "2024-02-29", "1" })).converted.date != null); try expect((try iso_eur.convertWith(&arena, &.{ "2000-02-29", "1" })).converted.date != null); try expect((try iso_eur.convertWith(&arena, &.{ "2023-02-29", "1" })).converted.date == null); // 1900 is divisible by 4 and by 100 but not 400. try expect((try iso_eur.convertWith(&arena, &.{ "1900-02-29", "1" })).converted.date == null);}
test "M1-CSV-017: the configured separator decodes the amount" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); const dotted = try iso_eur.convertWith(&arena, &.{ "2026-09-11", "1.50" }); try expect(dotted.converted.amount.?.eql(try Decimal.parseLiteral("1.5")));
var comma = iso_eur; comma.amount_format = .comma; const with_comma = try comma.convertWith(&arena, &.{ "2026-09-11", "1,50" }); try expect(with_comma.converted.amount.?.eql(try Decimal.parseLiteral("1.5"))); // The other separator is not accepted. const wrong = try comma.convertWith(&arena, &.{ "2026-09-11", "1.50" }); try expect(wrong.converted.amount == null);}
test "M1-CSV-020: a fixed currency applies to every row" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); const result = try iso_eur.convertWith(&arena, &.{ "2026-09-11", "1.50" }); try expectEqual(Currency.eur, result.converted.currency.?);}
test "M1-CSV-020: a field currency is read from the selected cell" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); var mapping = iso_eur; mapping.currency = .{ .column = 2 }; const result = try mapping.convertWith(&arena, &.{ "2026-09-11", "1.50", "USD" }); try expectEqual(Currency.usd, result.converted.currency.?);}
test "M1-CSV-021: only USD and EUR are supported" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); var mapping = iso_eur; mapping.currency = .{ .column = 2 }; for ([_][]const u8{ "GBP", "usd", "EURO", "", " EUR" }) |bad| { const result = try mapping.convertWith(&arena, &.{ "2026-09-11", "1.50", bad }); try expect(result.converted.currency == null); } try expectFmt("EUR", "{f}", .{Currency.eur}); try expectFmt("USD", "{f}", .{Currency.usd});}
test "M1-DIAG-004: independent failures in one row are all reported" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); var mapping = iso_eur; mapping.currency = .{ .column = 2 }; const result = try mapping.convertWith(&arena, &.{ "not-a-date", "N/A", "GBP" });
try expect(result.converted.date == null); try expect(result.converted.amount == null); try expect(result.converted.currency == null); try expectEqual(@as(usize, 3), result.diags.count()); for (result.diags.items.items) |d| { try expectEqual(@as(usize, 7), d.physical_row.?); try expectEqualStrings("bank.csv", d.file.?); }}
test "M1-CSV-022: a row that fails conversion is still returned, not dropped" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); const result = try iso_eur.convertWith(&arena, &.{ "bad", "N/A" }); // Conversion reports, it does not refuse: evaluation still has to run. try expect(!result.converted.isComplete());}
// -- configuration --------------------------------------------------------
const test_layout: fields.Layout = .{ .columns = &.{ .{ .field = .{ .alias = "d", .selector = .{ .index = 0 } }, .index = 0 }, .{ .field = .{ .alias = "a", .selector = .{ .index = 1 } }, .index = 1 }, .{ .field = .{ .alias = "c", .selector = .{ .index = 2 } }, .index = 2 }, }, .header_width = null, .minimum_width = 3,};
fn transactionOf(arena: *std.heap.ArenaAllocator, body: []const u8) Error!schema.Cursor { const alloc = arena.allocator(); const diags = diag.List.create(alloc) catch return error.OutOfMemory; const source = alloc.print("transaction:\n{s}", .{body}) catch return error.OutOfMemory; const root = schema.parse(alloc, "project.yaml", source, diags) catch return error.InvalidConfig; return root.child("transaction") catch error.InvalidConfig;}
fn loadWith(arena: *std.heap.ArenaAllocator, body: []const u8) Error!ColumnMapping { return ColumnMapping.load(try transactionOf(arena, body), test_layout, .dot);}
const valid_transaction = @embedFile("testdata/valid_transaction.yaml");
test "M1-CSV-016: a valid transaction mapping resolves its aliases" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); const mapping = try loadWith(&arena, valid_transaction); try expectEqual(@as(usize, 0), mapping.date_column); try expectEqual(Date.Format.iso, mapping.date_format); try expectEqual(@as(usize, 1), mapping.amount_column); try expectEqual(Currency.eur, mapping.currency.fixed);}
test "M1-CSV-016: only the listed date formats are accepted" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); const dotted = try loadWith(&arena, @embedFile("testdata/only_the_listed_date_formats_are_accepted_dotted.yaml")); try expectEqual(Date.Format.dotted, dotted.date_format); const short = try loadWith(&arena, @embedFile("testdata/only_the_listed_date_formats_are_accepted_short.yaml")); try expectEqual(Date.Format.dotted_short, short.date_format);
try expectError( error.InvalidConfig, loadWith(&arena, @embedFile("testdata/only_the_listed_date_formats_are_accepted_rejected.yaml")), );}
test "M1-CSV-017: only dot and comma are accepted as separators" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); const transaction = try transactionOf(&arena, valid_transaction); try expectEqual(Decimal.Format.dot, try ColumnMapping.loadAmountFormat(transaction));
const rejected = try transactionOf(&arena, @embedFile("testdata/only_dot_and_comma_are_accepted_as_separators.yaml")); try expectError(error.InvalidConfig, ColumnMapping.loadAmountFormat(rejected));}
fn amountFormatWith(arena: *std.heap.ArenaAllocator, separators: []const u8) Error!Decimal.Format { const body = std.mem.replaceOwned(u8, arena.allocator(), valid_transaction, " decimalSeparator: dot\n", separators) catch return error.OutOfMemory; return ColumnMapping.loadAmountFormat(try transactionOf(arena, body));}
test "M1-CSV-017: an optional group separator is none, dot or comma" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); try expectEqual( Decimal.Format{ .decimal = .comma, .group = .dot }, try amountFormatWith(&arena, " decimalSeparator: comma\n groupSeparator: dot\n"), ); try expectEqual( Decimal.Format{ .decimal = .dot, .group = .comma }, try amountFormatWith(&arena, " decimalSeparator: dot\n groupSeparator: comma\n"), ); try expectEqual( Decimal.Format.comma, try amountFormatWith(&arena, " decimalSeparator: comma\n groupSeparator: none\n"), ); // Absent means none. try expectEqual(Decimal.Format.comma, try amountFormatWith(&arena, " decimalSeparator: comma\n"));
try expectError( error.InvalidConfig, amountFormatWith(&arena, " decimalSeparator: comma\n groupSeparator: space\n"), );}
test "M1-CSV-017: the group separator must differ from the decimal separator" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); try expectError( error.InvalidConfig, amountFormatWith(&arena, " decimalSeparator: dot\n groupSeparator: dot\n"), ); try expectError( error.InvalidConfig, amountFormatWith(&arena, " decimalSeparator: comma\n groupSeparator: comma\n"), );}
test "M1-CSV-020: a currency field resolves to a column" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); const mapping = try loadWith(&arena, @embedFile("testdata/a_currency_field_resolves_to_a_column.yaml")); try expectEqual(@as(usize, 2), mapping.currency.column);}
test "M1-CSV-021: a fixed currency outside the supported set is rejected" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); try expectError( error.InvalidConfig, loadWith(&arena, @embedFile("testdata/a_fixed_currency_outside_the_supported_set_is_rejected.yaml")), );}
test "M1-CSV-016: a transaction field must name a declared alias" { var arena = std.heap.ArenaAllocator.init(std.testing.allocator); defer arena.deinit(); try expectError( error.InvalidConfig, loadWith(&arena, @embedFile("testdata/a_transaction_field_must_name_a_declared_alias.yaml")), );}