Why CSV bank statement imports fail, and how to fix them
Delimiters, encodings, header noise, total rows and split amount columns. The nine reasons a bank CSV will not import cleanly, with the fix for each.
By Karchu Editorial Team, reviewed against our editorial standards
CSV looks like the simplest possible format, which is exactly why it causes so much trouble. There is no real standard, so every bank and every accounting system exports something slightly different, and an importer that assumes the common case fails on roughly half of real files. Here are the failures we see most often and what each one actually needs.
1. The delimiter is not a comma
Semicolons are standard across much of Europe because the comma is the decimal separator. Tabs and pipes appear in exports from older banking systems. A file split on the wrong character produces either one giant column or amounts torn in half. The fix is detection, not configuration: work out the separator from the file itself.
2. The encoding is not UTF-8
UTF-16 exports are common from Windows banking software, and legacy code pages still appear. Read as UTF-8 they produce either gibberish or a file that looks like it has a null character between every letter. A byte order mark at the start also breaks the first header name, so the date column silently disappears.
3. There is a preamble above the headers
Bank name, account number, statement period, a blank row, then the real headers. Any importer that treats row one as the header will map every column wrongly. Scanning down for the row that looks like a header solves it.
4. There are totals below the data
Closing balance, page totals, a disclaimer paragraph. Imported blindly, these become transactions, and a total row imported as a transaction roughly doubles the month.
5. The amount is split across columns
Separate withdrawal and deposit columns, or debit and credit, are extremely common. So are single signed columns, brackets for negatives and Dr or Cr suffixes. All four have to be understood, and mixing them up flips the sign on half the file.
6. Number formatting is regional
1,23,456.78 in the Indian grouping, 1.234,56 in much of Europe, 1 234,56 with a space. Parsed with the wrong assumption these become wildly wrong values rather than errors, which is worse.
7. There are two date columns
Transaction date and value or settlement date frequently both appear. Which one belongs in a report depends on what you are doing, so both should be kept rather than one being discarded at import.
8. Quoted fields contain the delimiter or a newline
A narration containing a comma, or a multi line address inside one cell, will shear the row apart in any importer doing a naive split on the separator.
9. The workbook holds several accounts
Spreadsheet exports often put each account or month on its own sheet. Reading only the first sheet quietly loses most of the data, and concatenating all sheets without separating accounts produces a balance column that makes no sense.
How Karchu handles them
All nine are handled before any number is read: delimiter and encoding detection, byte order mark stripping, header scanning, footer trimming, multilingual column vocabulary, regional number parsing, dual date support, proper quoted field parsing, and per sheet processing for workbooks. Where a balance column is missing, one is inferred only when the data fully supports it and is labelled as derived. The details are on the CSV and spreadsheet converter page. For accounting system exports specifically, see the accounting export analyzer, and for the wider business document set, the business document analyzer.
Frequently asked questions
- Why does my bank CSV open as one long column in Excel?
- The file uses semicolons, tabs or pipes as separators and Excel assumed commas. Import it through the text import dialog and choose the correct delimiter, or use a tool that detects the delimiter automatically.
- Why are accented characters and currency symbols garbled?
- The file is UTF-16 or a Windows code page and is being read as UTF-8. Re open it specifying the correct encoding rather than fixing the characters by hand.
- Why does my import include a total row as a transaction?
- Because the importer read every row below the header. Totals and footnotes have to be trimmed, which is why an importer should detect the end of the data rather than reading to the end of the file.
- Do I need to delete the rows above the headers?
- Not with an importer that scans for the real header row. If your tool insists on row one being the header, delete the preamble but keep the header itself.