CSV vs Excel: when to convert, and the three things that go wrong

CSV is just text: one row per line, cells separated by commas. Every system exports it and every system imports it. XLSX is Microsoft’s format: multiple sheets, formatting, formulas, dates that know they are dates. Converting between them is a daily task, and three things regularly go wrong.

1. Leading zeros disappear

A postcode like 02139 or a product code like 000451 becomes 2139 and 451 when Excel decides the column is numeric. If the codes matter, keep them as text before converting, or fix the column type after.

2. Dates change shape

CSV has no idea what a date is; it is just characters. 03/04/2026 is 3 April in most of the world and 4 March in the US. Convert Files keeps the text exactly as it was, so what you see is what was written — but check the convention before you trust a column.

3. Only the first sheet is exported

CSV can hold one table. Converting a workbook with five sheets to CSV gives you the first one. If you need all of them, convert to .pdf or .docx instead, which puts every sheet in as its own table.

Beyond Excel

The same data also converts to JSON for developers, to SQL insert statements for a database, to Markdown for documentation, and to ODS for LibreOffice.

The leading zero problem

Open a CSV of postcodes, product codes, or phone numbers in Excel and watch the zeros disappear. 01234 becomes 1234, because the column looked numeric and Excel helpfully made it a number. The data in the file was never wrong; the interpretation on opening was. And if you then save, the corrected version is written back over the original.

The fix is to import rather than open — Data › From Text/CSV — and set the column type to Text before the data lands. Converting the CSV to XLSX first has the same effect, because the cell types are then recorded in the file instead of guessed at open time.

The date problem, which is worse

Excel converts anything date-shaped into a date. A part number like 3-10 becomes 3 October. A ratio written 1/2 becomes a date in January. The famous case is genetics: gene names such as SEPT2 and MARCH1 were being silently turned into dates so persistently that in 2020 the naming committee renamed the genes rather than keep fighting the spreadsheet.

This one is genuinely destructive, because the original text is gone once it has become a date serial number. There is no undo after saving. Import as Text, every time, for any column that is an identifier rather than a quantity.

The encoding problem

A CSV is text, and text needs an encoding. Write one in UTF-8 — which anything modern does — and open it in Excel without a byte order mark, and accented characters, currency symbols and anything non-English arrive as mojibake: é as é, and so on. The file is correct. Excel simply guessed the wrong encoding. Either save with a BOM or use the import dialogue and choose UTF-8 explicitly.

Delimiters are not always commas

In locales where the comma is the decimal separator — most of Europe — Excel writes and expects semicolons in its CSVs. A file exported in Germany and opened in the United States lands entirely in column A, and vice versa. It is not corrupt, it is punctuated for a different audience. This is also the strongest argument for TSV: a tab almost never appears inside a real value, so nothing has to be quoted or escaped and the ambiguity disappears.

What a CSV cannot hold at all

Formulas, formatting, colours, column widths, charts, pivot tables, comments, frozen panes, more than one sheet, and cell types. Converting XLSX to CSV keeps the values you can see and drops every one of those, permanently. That is not a flaw — it is the entire point of the format — but it is worth knowing before it is your only copy.

So when do you use which?

Use CSV to move data between systems: imports, exports, databases, scripts, anything where another program is the reader. Use XLSX when a person is the reader, or when formulas, multiple sheets or formatting carry meaning. Use TSV when the data itself is full of commas and quoting keeps going wrong. And keep the spreadsheet as the master copy, exporting CSV when something needs it, rather than the other way round.