Bank statement to CSV

If your bank offers a CSV for the period you need and your software accepts its layout, use it. In practice it often falls short. Many banks let you download transactions only for recent months, while statements go back years. Every bank's CSV has its own columns, date format and sign convention, so an accountant with clients at five banks maintains five import mappings. Most transaction downloads carry no opening or closing balance, so nothing tells you whether the file is complete. And for a closed account, a bank that has since merged, or a paper statement, there is no download at all — only the PDF.

Converting the statement PDF solves all four at once: any period you have a statement for, the same layout for every bank, balances that prove completeness, and a source that is the official document rather than an export.

A bookkeeper takes on a café that kept no books for its first year. The owner has twelve PDF statements from the business account; the bank's own download reaches back only a few months. The figures here are an illustration.

The bookkeeper merges the twelve statements in one go. Each is checked against its balances, the review confirms the chain from January to December is unbroken, and the merge downloads as a single Xero import CSV — Date, Amount, Payee, Description, Reference — which goes into Xero's Import a bank statement in one upload. The statement CSV and the PDFs go into the client file as the record behind it.

The statement CSV is a faithful copy of the PDF in text form. It opens with a short block of statement details, then a line that says TRANSACTIONS, then the transaction table with a header row and one row per transaction. Keeping the details in the file means the opening and closing balances travel with the transactions, so anyone who opens it later can check that the rows add up.

CSV is plain text, and Excel makes guesses when it opens it. The file FlowParse produces is written to keep those guesses on track: it is UTF-8 with a byte-order mark, so €, £, accented letters and Cyrillic come through intact; fields are separated by commas and quoted wherever a description itself contains a comma, a quote or a line break; and lines end the way Windows expects.

Two things are still worth knowing. In countries where Excel uses a comma as the decimal separator — much of Europe — Excel expects semicolons between fields, so double-clicking a comma-separated file can put everything in one column. Import it instead with Data › From Text/CSV and choose comma as the delimiter. And Excel likes to reinterpret dates and long numbers: an account number can turn into scientific notation and 03/05 can become a different date. If you need a spreadsheet rather than an import file, download Excel (.xlsx) directly from the bank statement to Excel converter — the columns arrive already typed.

A year of statements is twelve CSVs if you convert them one by one. With Smart Merge you upload them together and download one CSV: the statements are put in date order, columns from different banks are matched by meaning, every row carries the file it came from, and the merge review warns you when one statement's closing balance is not the next one's opening balance — usually a missing month. When the merge holds more than one kind of table, each table appears as its own block in the CSV under a title line.

The same merge also exports as one import CSV for Xero, QuickBooks and the other programs above, so a year of catch-up bookkeeping is one import instead of twelve.

Plain-text CSV is one of the most durable formats there is: any spreadsheet, database or text editor will open it in twenty years. Saved next to each statement PDF, the statement CSV makes an archive you can search across years — every payment to one supplier, every deposit over a threshold — without opening a single PDF.

Because the details block carries the account, period and balances, each CSV is self-describing. An auditor or a tax inspector who asks how a figure was arrived at can be shown the PDF, the CSV made from it, and the fact that the CSV's transactions add up from the printed opening balance to the printed closing balance. The PDF stays the official record; the CSV is the searchable copy. Name both files the same way — account, year and month — and the archive sorts itself: the PDF and its CSV sit side by side, and a year of statements lines up in order in any folder view or file listing.

A German, French, Spanish or Danish statement converts like an English one. Amounts written the European way — 1.250,00 — are read as 1250.00, so the CSV contains real numbers, and the column headings and details labels can be in English or kept in the statement's own language, whichever you choose when exporting.

The currency is recorded in the details block of the statement CSV and amounts are never converted. If you combine accounts in different currencies with Smart Merge, filter by source file before totalling, and apply your own exchange rate where you need one. Bank-by-bank details for more than a hundred banks are on the bank statement converters pages.