Why Excel mangles your CSV, and how to stop it
By the Banking Converter editorial team
Published
Excel does not read a CSV so much as guess at it: it infers the delimiter from your regional settings and converts anything that resembles a date or a number. That is why a reference beginning with a zero loses it and 03/04 becomes March. Each symptom has a specific cause, and most are avoided by exporting in a form Excel cannot misread.
Symptom to cause
| What you see | What happened |
|---|---|
| Every row in a single column | Excel expected a different delimiter, usually semicolon in European locales |
| 03/04/2026 shows as 4 March | Excel applied its own locale's date order, not the file's |
| A reference like 00123 became 123 | The column was read as a number, so leading zeros were dropped |
| A long reference became 1.23E+15 | The value was treated as a number and shown in scientific notation |
| Café shows as Café | The file is UTF-8, but Excel read it as a legacy single-byte encoding |
| 1,234.56 became 1 or an error | The thousands separator was read as the delimiter, splitting the amount |
| A description was cut in half | It contained the delimiter and was not quoted, or the quoting was not honoured |
Why Excel does this at all
CSV has no header declaring its delimiter, its encoding or its date order. The format is a convention rather than a specification, so a reader has to guess, and Excel guesses using the machine's regional settings rather than anything in the file.
That is the whole problem in one sentence: the same file opened on a machine in London and a machine in Berlin is parsed two different ways. It is not a bug so much as an unavoidable consequence of a format that carries no description of itself.
This is why a converted statement should be checked in the tool you are importing into, not just in Excel. A file that looks wrong in Excel may be perfectly correct, and one that looks right may have silently lost a leading zero.
Exporting so it cannot go wrong
Most of these problems are settled at export time rather than at import time.
- Choose the delimiter your locale expects. Comma for the UK and US, semicolon for most of continental Europe. Getting this right alone fixes the everything-in-one-column case.
- Export dates as YYYY-MM-DD. It is the one format Excel cannot reorder, because it is unambiguous.
- Keep the byte-order mark. A UTF-8 CSV that begins with a BOM tells Excel the encoding, which is what keeps accented merchant names intact.
- Prefer the Excel export when the destination is Excel. It writes real date and number cells, so nothing has to be inferred at all.
The CSV converter offers the delimiter and date format as choices, and writes UTF-8 with a byte-order mark by default. The Excel converter sidesteps the format entirely.
Opening a CSV without letting Excel guess
If you already have a file and cannot re-export it, do not double-click it. Import it, which lets you state what it contains.
- Open a blank workbook.
- Go to Data, then From Text/CSV.
- Select the file. Excel shows a preview with a File Origin and a Delimiter box.
- Set File Origin to UTF-8 and the delimiter to whatever the file actually uses.
- Choose Transform Data rather than Load.
- In the editor, set the type of each column by hand: Date for dates, Decimal Number for amounts, and Text for references, cheque numbers and account numbers.
- Close and Load.
The step that matters most is setting reference-like columns to Text. Once Excel has converted 00123 to 123, formatting the cell afterwards will not bring the zeros back — the information is gone.
Amounts, separators and negatives
Amounts cause trouble in three ways beyond the delimiter clash.
- Thousands separators. A well-formed export omits them: 1234.56 rather than 1,234.56. Every spreadsheet can add the display separator itself.
- Currency symbols. A leading $ or £ turns the value into text in some locales. Amounts should be plain numbers with the currency recorded in its own column.
- Negative styles. Banks print negatives as (125.00), 125.00 DR, or −125.00 with a typographic minus rather than a hyphen. Only the plain hyphen form is reliably read as a number.
Date,Description,Amount
2026-08-03,Card purchase Fresh Market,-84.17
2026-08-05,Payroll deposit,2500.00Exports here are written this way deliberately, which is why they import without a cleaning step.
Checking the file survived
Three quick tests catch nearly everything before the data reaches your books.
- Sum the amount column and compare it against the statement's own totals. If the statement prints total credits and debits, they should match exactly.
- Sort by date and check the first and last rows against the statement period. A date read in the wrong order usually shows up immediately as an impossible sequence.
- Spot-check one row with an accented character, one with a leading zero and one with the largest amount.
For the fuller version of this check, see how to reconcile a bank statement.
Frequently asked questions
Why does the same CSV open correctly for a colleague but not for me?
Your regional settings differ. Excel takes the delimiter and date order from the operating system, so the same file is parsed differently on two machines. Exporting with the delimiter your locale expects, and dates as YYYY-MM-DD, removes the difference.
Should I use CSV or Excel for a statement?
Excel if a person will open it, because dates and amounts arrive as real cells with nothing to infer. CSV if a program will read it, or if the destination asks for CSV specifically.
What is a byte-order mark and do I want one?
Three invisible bytes at the start of the file that identify it as UTF-8. Excel needs it to show accented characters correctly. Most other tools ignore it, so it is included by default; strip it only if a strict parser complains about the first column name.
Excel says the file is corrupt or unsafe. Is it?
Almost never. That warning usually appears when a file downloaded from the internet is opened directly. Save it locally, then use Data, From Text/CSV rather than double-clicking.
Other bank statement converters
About the author
The Banking Converter editorial team is the engineering group that builds and tests the conversion engine: the parsers for regional date and number formats, the reconciliation checks and the CSV, Excel, JSON, OFX and QBO exporters. Guides describe what the software does and what the formats require; they are not financial, legal or tax advice.