You export a clean list of contacts, open it in Excel, and it is wrong. Names with accents have turned into José. Everything is crammed into the first column. A product code that started with a zero has lost it. One address has become a date. Somebody downstream imports the file anyway and now the CRM is wrong too.
None of this is Excel being broken, and none of it is random. Each symptom has one specific cause, and once you can name the cause the fix takes seconds. This guide covers all of them, including the one that is a genuine security problem rather than an annoyance.
CSV is a convention, not a format
The root of every problem below is that comma-separated values is not a specification anyone is obliged to follow. There is an RFC — 4180, published in 2005 — but it documents common practice rather than mandating it, and it postdates most of the software that writes CSV files.
A CSV file is a text file. It carries no declaration of its character encoding, no statement of which delimiter it uses, and no type information for its columns. Every program that opens one has to guess all three, and different programs guess differently. That is the whole story. Everything that follows is a consequence.
Mangled accents: the encoding problem
You see José where José should be, or Müller instead of Müller. This distinctive pattern — one accented character becoming two odd ones — means the file was written as UTF-8 and read as a single-byte encoding, in this case usually Windows-1252.
In UTF-8, é is two bytes. Read one byte at a time under Windows-1252, those two bytes are à and ©. Nothing is lost — the bytes are intact — but the interpretation is wrong.
The reliable fix is a byte order mark: three specific bytes at the very start of the file that flag it as UTF-8. Excel on Windows honours it and switches encoding without asking. It is invisible in any text editor, costs three bytes, and eliminates the entire class of problem. A well-behaved export tool writes one; if you are generating CSV yourself, add it.
Without a BOM, the workaround is to import rather than open: in Excel, Data → From Text/CSV, which presents an encoding dropdown. Choosing UTF-8 there fixes the display. Double-clicking the file skips that dialog entirely, which is why the same file "works" for one colleague and not another.
Everything in column A: the delimiter problem
The whole row lands in one cell. This is a regional settings collision, and it catches people in Europe and Latin America constantly.
In locales that use a comma as the decimal separator — Germany, Spain, France, Italy, most of Latin America — Excel expects CSV files to be separated by semicolons, because commas are busy being decimal points. It does not sniff the file to check. It applies the locale rule and, finding no semicolons, concludes the row is a single value.
There are three ways out, in descending order of robustness:
- Use the import dialog. Data → From Text/CSV lets you pick the delimiter explicitly. Always works, on any locale.
- Add a separator declaration. A first line reading
sep=,tells Excel which delimiter to use regardless of locale. It is a Microsoft extension, not standard CSV, and other tools will read it as a data row — so use it only for files you know are going to Excel. - Ship XLSX instead. A real spreadsheet file has no delimiter to guess. See below.
Lost zeros and accidental dates
Excel type-guesses every cell on load, and its guesses are aggressive and lossy. Three of them cause real damage.
Leading zeros vanish. A postcode 01234 looks like a number, so it becomes 1234. Same for account numbers and any zero-padded identifier. The data is gone, not hidden — saving the file writes back the truncated value.
Things become dates. Anything shaped remotely like a date is converted, and the conversion is one-way. This famously forced the renaming of several human genes, becauseSEPT1 and MARCH1 kept becoming dates. In contact data it hits version strings and product codes.
Long numbers go scientific. A sixteen-digit identifier renders as 1.23457E+15 and, worse, is stored at reduced precision. The trailing digits are genuinely lost.
Email addresses themselves are mostly safe, because an @ makes the value unambiguously text. The damage lands on the columns you exported alongside them. Import via the dialog and set the vulnerable columns to Text, or accept XLSX where each cell carries its own type and no guessing occurs.
Quotes, commas and line breaks inside a field
A field containing a comma has to be wrapped in double quotes, or it reads as two fields. Company names do this constantly: Acme, Inc. becomes two columns and every field after it shifts left by one. In a contact list this silently pairs each person with the wrong company.
A quote inside a quoted field is escaped by doubling it, not with a backslash. So Dana "Danny" Wright is written as "Dana ""Danny"" Wright". Writers that use a backslash produce files that most parsers misread.
A field may contain a newline provided it is quoted — a multi-line address, for instance. This is legal RFC 4180 and it breaks every home-made parser that reads the file line by line. If you are consuming CSV in code, use a real parsing library. The naive split(",") approach fails on the first quoted comma it meets, and it will meet one.
On line endings: the RFC specifies CRLF. Unix tools write LF. Excel accepts both. Some older Windows software does not. Writing CRLF is the safer default for a file you are handing to someone else.
CSV injection: the one that is a security issue
This one deserves its own section because it is not a formatting annoyance, and most export code does not handle it.
When a spreadsheet opens a cell whose value begins with =, +, - or @, it treats the contents as a formula rather than text. If any part of your data is user-supplied, an attacker can put a formula in it — and formulas can reach outside the spreadsheet. Excel's WEBSERVICE function fetches a URL; HYPERLINK can smuggle cell contents into a link; older systems allowed launching external commands, prompting a warning most users click through.
The realistic attack is mundane: someone signs up with a display name or company field containing a formula, an operations person exports the contacts, opens the file, and the formula executes with their permissions on their machine.
The defence is simple and belongs in the export, not in the user's habits: prefix any value starting with one of those four characters with a single quote, or wrap it so the spreadsheet reads it as literal text. It costs one line of code and it is the difference between an export and a delivery mechanism. Any export you build for other people to open should do this by default.
When to use XLSX instead
XLSX sidesteps most of the above by construction. It is a zipped set of XML documents, and it states its own encoding, holds no delimiter to guess, and records an explicit type for every cell. Accents survive. Leading zeros survive if the cell is typed as text. Nothing becomes a date on its own.
Prefer XLSX when the file is going to a person who will open it in a spreadsheet, when the data contains accented characters or identifiers with leading zeros, or when you want a header row that is visibly a header.
Prefer CSV when the file is going into another system rather than to a human, when it is large enough that size matters, when it needs to be readable in a text editor or diffable in version control, or when the receiving tool only accepts CSV — which many CRMs and sending platforms still do.
A useful habit when handing a list to someone whose tooling you do not control: export both. They cost nothing to produce and it removes an entire round trip.
A checklist for exports that survive
If you are producing files other people open, these eight things cover nearly everything:
- Write UTF-8 with a BOM. Three bytes, and accented names stop breaking.
- Quote every field that contains a delimiter, a quote or a newline, and double the internal quotes.
- Neutralise formula-leading characters before writing. Not optional if any field is user-supplied.
- Use CRLF line endings for maximum compatibility.
- Include a header row with stable, machine-friendly names — no spaces, no renaming between exports.
- Split compound values into separate columns. An address split into email, domain and TLD is sortable and pivotable; one column is not.
- Offer XLSX alongside for anyone opening the file by hand.
- Test the actual round trip — open your own export in Excel, on a machine with a European locale, before you send it to a client.
That last one catches more than the other seven combined. The failure modes here are environment-specific, which is exactly why they survive testing on the developer's machine and appear on the customer's.
The exports from the extractor on this site follow this list: UTF-8 with a BOM, RFC 4180 quoting, CRLF endings, the formula-injection guard, and separate Email, Domain and TLD columns in both CSV and XLSX.