Excel to CSV: What a Spreadsheet Loses on the Way to Plain Text

A workbook can hold dozens of sheets; a CSV holds one table. There is no place in the format to say that a second table follows, because a CSV is defined as rows of fields separated by a delimiter. A converter therefore has to choose: export the first sheet, the active sheet, or ask you which one. Silent first-sheet export is the usual behaviour and the usual source of a file that looks truncated.

If you need several sheets, export them as separate CSV files and name them by sheet, or use a format that can hold multiple tables. Excel's own xlsx is a zip of XML; ODS works similarly; Parquet can hold a schema and multiple columns efficiently. CSV is for feeding one table into one import step, not for archiving a workbook.

A CSV stores the displayed value, not the formula that produced it. That is normally what you want for an import, since the receiving system cannot recalculate a formula it does not have. The trap is that the displayed value depends on formatting: a cell showing 12.30 percent might export as 0.123, and a cell showing 1,234.50 might export as 1234.5. Currency symbols are display formatting and disappear, so the receiving system has no idea what the number means.

Dates are worse. A spreadsheet stores a date as a serial number with a format applied, and CSV export often writes either the serial number or a locale-formatted string such as 03/04/2026, which is ambiguous to half the world. If the destination expects ISO dates, format the column as yyyy-mm-dd before exporting, or convert afterwards. The same applies to percentages and to numbers stored as text.

CSV has no declared encoding, so the reader has to guess. UTF-8 is the correct default, but Excel on Windows will misread a plain UTF-8 file containing accented characters or non-Latin scripts unless it sees a byte-order mark. Writing UTF-8 with a BOM fixes Excel at the cost of confusing some strict parsers, which may treat the BOM as part of the first header name. Know which consumer you are feeding.

The delimiter is the other portability problem. Comma is standard, but locales where the decimal separator is a comma often export with semicolons so the two do not collide. If your values contain commas, the fields must be quoted, and if they contain quotes those must be doubled. A correctly escaped CSV is unambiguous; a hand-edited one usually is not. For values that are only tab-separated, TSV avoids the quoting mess entirely, which is why data tools often prefer it.

Postcodes, phone numbers, account numbers and product codes are identifiers, not quantities, but a CSV has no types. A value like 01234 is written faithfully, and then a reader that infers types turns it into 1234 and destroys the identifier. Large integers suffer the same way: a 19-digit ID exceeds the precision of a double and comes back rounded. The usual defence is to keep the column as text in the source and, where the reader allows it, declare the column as text on import.

Scientific notation is the visible symptom of this. If a part number appears as 1.23E+18, it has already been parsed as a number and the original digits are gone. Fix it at the export stage by prefixing or quoting, not by editing the CSV in a spreadsheet that will re-corrupt it on save.