CSV vs XLSX

A CSV is a grid of values and nothing else — no types, no formulas, no formatting, no second sheet, no idea which row is a header. It is the smallest possible way to write a table down, and every program on earth reads it.

An XLSX is a spreadsheet: several sheets, cell types, formulas that recalculate, charts, conditional formatting, frozen panes, data validation, and the record of who last touched it. It is a document that happens to contain a table, where a CSV is a table that happens to be a file.

This is the reason most people arrive here, and it is worth being precise about where the fault lies. The CSV is a text file containing exactly what was written. Excel, on opening it, guesses a type for every column and rewrites what it sees.

Leading zeros disappear, so a postcode or a product code becomes a number. Anything that looks like a date becomes one, which famously renamed a set of human genes and still mangles part numbers daily. Long identifiers past fifteen digits lose their last digits to floating point. Text in an unexpected encoding becomes question marks. None of this is in the file; all of it is in the copy Excel saves back.

The way round it is to import rather than open — Data, then From Text/CSV — and set the problem columns to Text before loading. Or to hand over XLSX in the first place, where the types are declared and nothing is guessed.

A CSV carries no declaration of its character encoding. UTF-8 is the sane default and is what nearly everything writes, and Excel on Windows has historically assumed the regional code page instead — which is why an export full of German umlauts or Polish diacritics opens as gibberish.

The usual workaround is to write the file with a byte order mark, those three invisible bytes that tell Excel it is UTF-8. It works, and it breaks other things: many parsers then see the mark as part of the first column heading, and a match against "id" silently fails. XLSX has no such problem, because encoding is part of the format rather than a guess.

CSV, nearly always. Import routines, databases, statistical tools, data pipelines and command-line utilities all read it directly, it streams so a file larger than memory is still processable, and there is nowhere for anything unexpected to hide.

That last point is not decorative. An XLSX can contain macros, external links and formulas that reach out to other files, which is why security-conscious systems refuse the format outright and ask for CSV. A CSV cannot execute anything.

XLSX. It opens with the columns already the right width, the headers already bold, the numbers already formatted, and the types already correct — no import dialog, no guessing, no support conversation about why the postcodes are wrong.

It also survives being worked on. The recipient will filter it, sort it, add a column and total something, and all of that is what a spreadsheet is for. A CSV sent to a colleague usually becomes an XLSX within ten minutes anyway; sending one saves the step and the damage that happens during it.

XLSX is a ZIP of XML, so it compresses. CSV is raw text and does not. On a large export of numbers the XLSX is frequently the smaller file — sometimes dramatically, because repeated values and shared strings compress extremely well.

What CSV wins is memory and streaming. A two-gigabyte CSV can be read line by line by a tool that never holds more than one row; the equivalent XLSX has to be unzipped and parsed as a document. For very large data the CSV is the workable one, whatever the number on disk says.

A worksheet stops at 1,048,576 rows and 16,384 columns. That number is two to the twentieth and it has been fixed since 2007, so it is not a setting anybody can raise. A CSV has no limit at all beyond the filesystem.

This means an export can genuinely be impossible to open as a spreadsheet, and the failure is quiet rather than loud — some tools import the first million rows and say nothing about the rest. If a CSV is comfortably over a gigabyte, a spreadsheet is the wrong destination for it and the honest answer is a database or a Parquet file, not a workaround.

A CSV is text, so Git stores it as text, diffs it line by line and merges it. A change to one figure shows up as one changed line that a reviewer can read in the pull request.

An XLSX is a ZIP archive full of compressed XML. Git stores each version whole, shows `binary files differ`, and cannot merge two people's edits at all. For anything that lives in a repository — reference data, fixtures, a lookup table — that difference outweighs every advantage the spreadsheet format has, and it is why so much configuration that could have been a workbook is a CSV instead.

A workbook can carry macros, and a macro is a program that runs on the machine that opens it. That is why mail systems quarantine spreadsheets from outside the organisation, why the `.xlsm` extension exists to make the capability visible, and why opening an unexpected workbook is a genuinely different act from opening an unexpected CSV.

A CSV has no such surface: it is text, and nothing in it executes. The qualification worth knowing is that a spreadsheet application can still be tricked by one — a cell beginning with `=` is treated as a formula on import, which is a real injection route into whoever opens it next. The file is inert; the program reading it is not.

XLSX to CSV loses everything that is not the values of one sheet: the other sheets, the formulas — you keep their results — the formatting, the charts, the merged cells, the comments, the frozen panes. If the workbook is genuinely a document, that is most of it.

CSV to XLSX loses nothing, because the source had nothing to lose. What it gains is declared types, which is the point: converting before sending is how you stop the recipient’s Excel from guessing. Keep the CSV as the machine-readable master and generate the XLSX for people.