How to convert between CSV and Excel

Convert without opening Excel — which also avoids the changes Excel makes to your data on the way in.

CSV is plain text; .xlsx is a spreadsheet with types, formatting and multiple sheets. Converting directly avoids a set of well-known problems that arise when Excel opens a CSV.

Step-by-step

1. Add your file, CSV or .xlsx.
2. Convert.
3. Download.

The damage Excel does to CSVs

• Leading zeros are stripped. Postcodes and account numbers become numbers, and the zeros go.
• Things that look like dates become dates. This is the notorious one: gene names and part numbers silently converted, and the change is irreversible once saved.
• Long numbers become scientific notation, losing digits from identifiers.
• Accented characters break when the encoding is guessed wrongly.

These happen on opening, so a round trip through Excel can corrupt a file even if you change nothing and save it back.

Encoding

UTF-8 is used throughout, which is what keeps accented characters and non-Latin scripts intact. Most CSV corruption of the "é" variety is a file written in one encoding and read as another.

When you actually want Excel

Use .xlsx when formulas, formatting or several sheets matter. Use CSV when the data is the point and you want it readable by anything. CSV holds no formulas — converting a spreadsheet to CSV keeps the computed values and discards how they were computed.

Import it, do not open it

Nearly all of the damage happens because a CSV was opened by double-clicking, which gives Excel no opportunity to ask anything and no instruction about types. The alternative takes twenty seconds and removes the entire class of problem: use the import path — Data, then From Text/CSV — which shows you a preview and lets you set each column's type before a single value is parsed.

The columns worth setting to Text explicitly are anything that is an identifier rather than a quantity: reference numbers, postcodes, phone numbers, product codes, anything with leading zeros. If it is never going to be added up, it is not a number, and telling Excel so at import is the only reliable moment to do it.

What conversion cannot undo

The important thing about the automatic conversions is that several of them are lossy, so noticing afterwards does not help. A leading zero stripped from an identifier is gone — formatting the column back to text gives you the shortened value with no zero restored. A number beyond fifteen significant digits, which includes plenty of long account and card numbers, has had its tail replaced with zeros and cannot be recovered from the cell.

Dates are the subtle one, because the damage is invisible rather than obviously wrong. A value read under one locale as the third of April and under another as the fourth of March is a valid date either way, and nothing about the resulting cell says which reading happened. This is the reason the standard advice for anything you care about is to keep the original CSV unmodified and treat the spreadsheet as a working copy, rather than to convert in place and hope.

Frequently asked questions

Will my leading zeros survive?

Converting directly preserves them. Opening a CSV in Excel is what strips them, which is a good reason to convert without going through Excel at all.

Do formulas convert?

Going from .xlsx to CSV keeps the results, not the formulas — CSV has no way to store them. Going the other way produces values rather than formulas.

Which encoding is used?

UTF-8, which keeps accented and non-Latin characters intact.