CSV or Excel for bank statement data: which should you use?

What each one actually is

A CSV is text. Values separated by commas, rows by newlines, and nothing else — no types, no formatting, no formulas. Everything in it is a string until something else decides otherwise.

An .xlsx is a compressed set of XML documents recording types, formats, formulas and multiple sheets. A number is stored as a number and a date as a date.

The CSV traps

• Dates carry no order. 03/04 is 3 April or 4 March depending on who reads it, and the reader decides using its own locale rather than anything in the file. Days above the twelfth are rejected; days below are read wrongly and accepted, so a partial rejection means the accepted rows are suspect too.
• Thousands separators collide with the delimiter. 1,234.56 in a comma-delimited file splits into two columns and shifts everything after it.
• European numbers are worse: 1.234,56 does the same thing while looking correct.
• Long reference numbers open in a spreadsheet as scientific notation, and saving from there writes the corrupted form back permanently.

The first is the dangerous one because it is silent. Write dates as YYYY-MM-DD and it goes away entirely.

The Excel traps

• Many bank statement importers do not accept .xlsx at all. Xero, Wave and FreshBooks want CSV for statement uploads.
• Opening a CSV in Excel and saving it back applies Excel’s own interpretation — reference numbers become scientific notation, leading zeros vanish, and text that resembles a date becomes one.
• Formatting a column to look like a date changes only the display. The underlying misreading survives the export, which is why repairs have to go into a new column by formula rather than by reformatting.

The rule that works

Use Excel when a person is going to look at the data — reviewing a conversion, filtering by date, checking totals before anything is imported. Types are preserved and nothing is silently reinterpreted.

Use CSV when software is going to read it, and generate it directly rather than by saving an Excel file as CSV. And if the target accepts OFX, prefer that over both: it states its dates and signs unambiguously, which is the entire source of trouble here.

Delimiters and encoding, briefly

CSV is not quite one format. In much of Europe the separator is a semicolon rather than a comma, because the comma is the decimal separator — and a file saved from a spreadsheet configured that way will open as a single column everywhere else.

Encoding matters for the same reason it always does: a statement containing an accented payee name, saved as something other than UTF-8, arrives with the accents replaced by punctuation. Where an importer offers an encoding choice, UTF-8 is the answer.

Opening a CSV without corrupting it

Double-clicking a CSV hands it to Excel with every default applied, and the defaults are destructive for statement data: long references become scientific notation, leading zeros disappear, and anything resembling a date is converted to one. Saving from that state writes the damage back permanently.

1. 1Open Excel first, with no file.
2. 2Use Data → From Text/CSV rather than opening the file directly.
3. 3Set the columns that hold references or account numbers to Text before importing.
4. 4If you only need to read the file, do not save it. Anything you save is Excel’s interpretation, not the original.

The same caution applies to the round trip people make constantly — open a converted CSV, glance at it, save, then import. That import is now importing Excel’s guesses.