CSV vs Excel — what actually differs

A CSV is text. That is the whole format.

A CSV is lines of characters separated by commas. There is nothing else in it — no cell types, no formulas, no formatting, no multiple sheets, no column widths. `007` in a CSV is three characters, not a number and not a string; the file does not say which.

An .xlsx file is the opposite: a compressed bundle that records, for every cell, what kind of value it holds and how it should look. That is why it is bigger and why it survives a round trip unchanged.

What Excel does to your data when it opens a CSV

Because the file does not declare types, Excel infers them. Its guesses are reasonable in the average case and destructive in specific ones:

• Leading zeros disappear. `007` becomes `7`. Postal codes, account numbers and part numbers are the usual casualties.
• Things that look like dates become dates. `1-2` becomes 2 January. This famously forced geneticists to rename genes because Excel kept turning SEPT2 into a date.
• Long numbers switch to scientific notation and lose their tail. A 16-digit identifier can come back as `1.23457E+15`, and the lost digits are not recoverable by changing the format afterwards.
• The damage is saved when you save. The original file was correct; the file Excel writes back is not.

Why your CSV shows as gibberish, or all in one column

Two separate problems, both about assumptions the file cannot state.

Encoding. A CSV does not record its character encoding. Excel on Windows historically assumes the local codepage rather than UTF-8, which is why Korean, Japanese and accented Latin text arrives mangled. A UTF-8 byte order mark at the start usually fixes it.

The separator. In countries where the comma is the decimal mark, spreadsheets write and expect semicolons instead. Open such a file elsewhere and every row lands in a single column. The file is not broken; the two programs disagree about what a comma means.

When the file is simply too big

A spreadsheet has a hard ceiling of about 1,048,576 rows, and it gets slow long before that because it loads everything into memory to make it all editable.

Past a certain size the right move is to stop opening the file and start asking it questions instead. `SELECT` a few columns, filter, group, count — you get an answer in seconds without the machine trying to render millions of cells you were never going to look at.

Parquet, briefly

If CSV keeps letting you down, Parquet is what the analytics world switched to. It stores types inside the file, so nothing is guessed. It stores data by column rather than by row, so reading three columns out of fifty reads roughly three columns' worth of bytes. And it compresses well — often five to ten times smaller than the same CSV.

The trade is that you cannot open it in a text editor. It is a format for querying, not for eyeballing.