Bank CSV import

The import reads an XLSX, CSV or PDF statement of booked payments. There are presets for some layouts, other layouts are read automatically, and a column mapper remains as a last resort. It has been tested with Holvi exports so far. A mapping says which column holds which value. A layout the app works out, or that you set in the column editor, is saved on a successful import and used again for files with the same column names.

Flow

The account is, first hit: the one chosen on the question, the only account, the account whose bank the layout names, a new account named for that bank when there is none, else the question. The layout is, first hit: a layout saved for these column names, the built-in layout for them, the model’s proposal when it reads the whole file, else the column editor. The editor also opens when rows have errors, the stated totals do not add up, or the delimiter or a slash date order is ambiguous. A file that is not UTF-8 is read as Windows-1252.

In the editor, the preview reads the whole file and shows up to 50 rows and up to 100 row problems. Any row problem blocks the import. A preview is valid for 30 minutes and is signed. After import the browser goes to Bank for the month of the first row, where the banner offers Undo for the whole import.

Column editor fields

The editor shows these with the file it was given.

A new account is created only when the import is confirmed. The editor’s Text encoding sits under The file.

Mapping fields

Shown under The columns look wrong? Adjust how we read the file. Column numbers in the app start at 1; row numbers for Which row has the column names? start at 0.

The file

Dates

With Sometimes, a date may be followed by T or a space and HH:MM, optional seconds and fractions, and an optional Z or +HH:MM offset. The printed calendar day is kept. A date format with slashes needs the tick The day and month order above is right for dates written with slashes.

Amounts

dot-or-comma takes whichever of . or , appears and cannot be combined with a thousands separator. space accepts a space, a no-break space or a narrow no-break space. Groups must be 3 digits after a first group of 1 to 3. Money out is stored as a negative amount.

Currency and names

Already imported?

Saved layouts

There is no form for this. A layout the model proposed or you set in the editor is saved when the import succeeds, with a fingerprint of the normalised column names, and is used for files whose column names give the same fingerprint, the account’s bank first. Built-in and saved layouts are not saved again. A failure to save the layout does not undo the import.

Accepted formats

Built-in layouts

Built-in layouts are matched by column names. They were tested with synthetic files only and are named after the CSV layout, not the bank. Presets match common CSV export layouts; Nuits Receipts is not affiliated with any bank.

When exactly one layout matches, it is applied automatically. When several match, you choose one or Custom mapping.

Column names row

For a new file, the row with the column names is found automatically: the first row of two or more names with no gaps, none of them a number or a date, whose following rows fit under it. Rows above it, such as an account line or stated totals, are left out and listed under the preview. When no row qualifies, the first row is used. Which row has the column names? overrides it.

Stated totals

Above the column names, a label that names a volume, sum or amount, or a count or number, of credits or debits (for example Total volume of credits and Total count of debits) is read with the figure in the cell below it. Credits are compared with the payments read as money in and debits with money out. Opening balance and Closing balance are read the same way: the opening balance plus every payment must come to the closing balance. A different count, sum or balance is a problem and blocks the import.

Layouts read automatically

When no saved or built-in layout matches, one request goes to Workers AI (Clef). It sends only the column names and up to 5 rows, each cell cut to 40 characters, and asks which column holds the date, the amount (or money out and money in), the currency, the payee, a description, a reference and the transaction ID, plus the date format and how money out is shown. The answer can only name columns of the file. The decimal mark and the date format are confirmed against the sample rows, and the account currency is used when there is no currency column.

The proposal is used only if the whole file then reads with no row problems. Otherwise, or if the request fails, the page asks for the columns as before. The proposal travels with the preview, so Check again and Import make no further requests. With AI_MODE mock, columns are picked by their names instead.

PDF statements

A PDF statement is turned into a CSV on upload, with the statement’s printed balances and totals above the column names, so the checks above apply to it. The text is read with its position on the page, which keeps the columns apart.

• Holvi (EUR - ACCOUNT STATEMENT, columns Booking date, Payment information, Transaction no., Amount EUR) is read by its layout, with no model request. Each payment becomes a row of the Holvi statement layout: the booking date, the signed amount, the other party from the payment information (an IBAN line left out), the payment type, the Message and Reference parts, the Currency and amount line of a card payment abroad, and the Filing ID as the transaction ID. The BALANCE lines and the TOTAL NUMBER OF DEPOSITS and WITHDRAWALS lines are the stated figures. A line in no known place stops the read instead of being skipped.
• Other layouts are cut into one block per payment: a line that starts with a date, up to the next one. Lines printed in the same place on most pages, such as page headers and footers, are left out, and labelled opening and closing balance and total lines are read as stated figures. Workers AI (Clairic) reads the date, amount, currency, other party, description, reference and transaction ID of each block, 20 blocks per request and up to 4 requests at a time, at most 1,000 blocks. Each value is then checked against the block’s own text: the amount must be printed in it, the date must be a reading of its leading date, and text values must appear in it. A value that fails, or that Clairic is not sure of, is left blank, so the row is listed as a problem.

The PDF is read once, on upload. Check again and Import use the CSV it became, which is also what is stored as the original. With AI_MODE mock, other layouts are read by a fixed local reader instead.

Column name aliases (Holvi layout)

Column names are compared after normalisation (lower case, accents removed, punctuation as spaces).

Row problem codes

Shown in the preview as “Row N, field: message (code)”.

Duplicates and re-imports

A row’s identity is a hash of the account and either the transaction ID or its facts: booking date, amount, currency, normalised merchant, reference, original amount and original currency, plus a count of equal rows before it in the file.

Limits