CSV to Excel: encodings, delimiters, and the leading-zero problem

The leading-zero problem

This is the classic. Your CSV contains a column of German postcodes: 01067, 04109, 09111. You open it in a spreadsheet, and they become 1067, 4109, 9111.

Nothing was corrupted in the file. The CSV stored the characters `01067` faithfully. The spreadsheet looked at those characters, decided they represented a number, and displayed the number — and numbers do not have leading zeros. The same thing happens to phone numbers, SKUs, account numbers, ISBNs, and any identifier that happens to be made of digits.

This is not a conversion bug you can fix after the fact. Once the sheet has been saved with the column typed as a number, the zeros are gone from the data as well as the display. You have to prevent it on the way in.

• Import rather than open. In Excel, use Data → From Text/CSV, and set the column type to Text before loading.
• Quoting a CSV value does not guarantee a text cell: the importer may still infer a number. Set identifier columns to Text explicitly before loading them.
• XLSX can preserve explicit cell types once the data has been read correctly. Converting to XLSX does not restore zeros already lost during import.

Encoding: why your file is full of é and ا

A CSV has no header declaring its character encoding. A file written as UTF-8 and read as Windows-1252 turns `é` into `é` and Arabic text into a run of Latin letters that look like line noise.

The most common version of this is Excel on Windows opening a UTF-8 CSV. Historically Excel assumed the system's legacy codepage unless the file started with a byte-order mark. A UTF-8 file with a BOM opens correctly; the identical file without one does not.

There is no way to tell from looking at a CSV which encoding it is in — you can only infer it from whether the result reads as words. If you are producing CSVs for other people, write UTF-8 with a BOM. If you are consuming one that arrives mangled, re-import it and specify the encoding explicitly rather than trying to find-and-replace your way out.

Delimiters are not always commas

In locales that use a comma as the decimal separator — most of Europe, much of Latin America — spreadsheet software writes CSVs with semicolons instead, because a comma is already taken. The file is still called .csv.

Tab-separated files are also common, especially out of databases and analytics tools, and are sometimes named .csv anyway.

So a CSV that opens as a single column of text in one country and as a clean table in another is not broken. It is separated by a character the importer was not expecting. Specify the delimiter at import time.

What conversion actually gains you

XLSX stores cell types and workbook structure without relying on CSV delimiters. However, conversion still has to interpret the source encoding, delimiter, and column types correctly. Check identifiers and dates before saving the result.

The conversion runs through LibreOffice's Calc filter, which parses the CSV and writes a real spreadsheet. Check the first few rows of the output before you build anything on it — that is where a delimiter or encoding misread will be obvious.

Going the other way: what XLSX to CSV throws away

• Every sheet but one. CSV is a single table; a workbook with five tabs converts to the first sheet and the rest are gone.
• Formulas. They are exported as their last calculated value. If the numbers need to stay live, CSV is the wrong destination.
• All formatting — cell colours, conditional formatting, column widths, number formats. A cell displaying `$1,234.50` may well export as `1234.5`.
• Charts, images, pivot tables, and comments. None of these have any representation in a text file of comma-separated values.
• Cell types. Once it is CSV, `01067` is at the mercy of the next program that reads it, all over again.

A workflow that does not bite

Keep the authoritative copy in XLSX. Export to CSV only at the point where something specifically requires CSV — a database import, an API upload, a legacy tool — and treat that CSV as disposable output rather than a version of your data.

When you receive a CSV from someone else, convert it to XLSX before you start working, and check the identifier columns in the first ten rows. Two minutes there saves finding out three weeks later that every account number in the file lost a digit.