The choice looks trivial and is not. One of these silently corrupts long reference numbers; the other silently changes what your dates mean. Both failures import cleanly, which is what makes them expensive.
What each one actually is
A CSV is text. Values separated by commas, rows by line breaks, and nothing else: no types, no formatting, no formulas, no sheets. Everything in it is a string until the program reading it decides otherwise, and each program decides differently. The closest thing to a standard is RFC 4180, which says a value containing a comma, a quote or a line break must be wrapped in double quotes, with any quote inside it doubled.
An .xlsx is a compressed package of XML documents recording types, number formats, formulas and multiple sheets. A number is stored as a number and a date as a date, so once a value is correct in a workbook it stays correct on any computer, whatever its regional settings.
| CSV | Excel (.xlsx) | |
|---|---|---|
| Stores types | No, all text | Yes: numbers, dates, text |
| Date meaning | Decided by the reader | Fixed once stored |
| Accepted by statement importers | Almost all | Few: Sage, some Tally and bank-specific routes |
| Multiple sheets | No | Yes, e.g. transactions plus a reconciliation |
| Safe to open by double-clicking | No, Excel reinterprets it | Yes |
| Best for | Software reading it | People reading it |
Most mapping failures come from a generic export rather than from the data, so a CSV written for the software you are importing into (Xero preset, QuickBooks three-column, or plain) removes a step that has no reason to exist.
The CSV traps
- Dates carry no order. 03/04/2026 is 3 April or 4 March depending on who reads it, and the reader decides using its own settings 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. An unquoted 1,234.56 in a comma-delimited file splits into two columns and shifts everything after it. Quoting fixes it; leaving separators out entirely is better.
- European numbers are worse: 1.234,56 is either split or read as something near one, while looking correct on screen.
- Signs are a convention, not a rule. One file has a single signed Amount column; another has separate Debit and Credit columns, both positive. Mapping the wrong one reverses every transaction.
- 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, the ISO 8601 form, and it goes away entirely, because no reader interprets 2026-03-04 as anything but 4 March. Where an importer insists on a particular layout, match it exactly and confirm the date format on the import screen.
Date,Description,Amount,Reference
2026-03-15,"SHELL OIL, LONDON",-45.20,
2026-03-16,ACME LTD,1250.00,INV-1042The Excel traps
- Most bank statement importers do not accept .xlsx at all. Xero, QuickBooks Online, Wave, FreshBooks and MYOB want CSV, OFX or one of the Intuit formats. Sage Accounting is the notable exception, accepting Excel statement files.
- Excel keeps only 15 significant digits. A 16-digit card number or a long transaction reference typed or imported as a number has its last digits replaced with zeros, and no amount of reformatting brings them back.
- Opening a CSV in Excel and saving it back applies Excel’s own interpretation: reference numbers become scientific notation, leading zeros vanish from account and sort codes, and text that resembles a date becomes one.
- Formatting a column to look like a date changes only the display. A date that was misread on the way in stays misread, which is why repairs have to go into a new column by formula rather than by reformatting.
What each importer expects
Where the destination is accounting software, its own CSV rules matter more than any general advice. The common ones are narrow and different from each other:
| Software | Columns | Watch for |
|---|---|---|
| QuickBooks Online | Date, Description, Amount; or Date, Description, Credit, Debit | Three or four columns only; extra columns must go |
| Xero | Date and Amount required; Payee, Description, Reference help matching | Remove balance columns; confirm the date format on import |
| Wave, Zoho Books, FreshBooks | Mapped on screen | Separate debit and credit columns need care with signs |
| Tally Bank Statement import | Bank’s own download format | Files converted from PDF are refused; use XML vouchers instead |
A balance column is the most common thing to remove. Importers build the balance themselves from the transactions, so a balance column is at best ignored and at worst mapped by mistake as an amount.
The rule that works
Use Excel when a person is going to look at the data: reviewing a conversion, filtering by date, totalling by payee, checking the closing balance 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, and it carries transaction IDs that let the importer recognise repeats.
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 opens as a single column everywhere else. A file that arrives as one wide column is almost always this.
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. Excel adds a twist: when you double-click a UTF-8 CSV it only recognises the encoding if the file begins with a byte-order mark, which is what Excel’s own “CSV UTF-8” save option writes. 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. Saving from that state writes the damage back permanently.
- 1Open Excel first, with no file.
- 2Use Data → From Text/CSV rather than opening the file directly.
- 3Check the delimiter and the file origin (encoding) in the preview, then choose Transform Data rather than Load.
- 4Set the columns that hold references, card or account numbers to Text, and set the date column using the file’s actual date order.
- 5If 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. If you want to look first, ask for an Excel file for looking and a separate CSV for importing.
Checking either one before it goes anywhere
Whatever the format, the check that catches a bad conversion is the same: take the statement’s opening balance, add every amount in the file, and compare the result with the closing balance printed on the statement. Then compare the row count with the transactions on the page. A dropped row, a doubled row or a reversed sign all show up as a difference, and finding it before import takes minutes where finding it at reconciliation takes an afternoon.