Open a CSV file

Reading a CSV without letting Excel touch it

The reason to look at a CSV here rather than in Excel is that Excel edits it while opening it. A leading zero on a postcode or an account number is stripped, because the column looks numeric. An identifier longer than fifteen digits is rounded. Anything resembling a date is reformatted into the local convention, so 03/05 changes meaning depending on the country. And a gene name like SEPT1 becomes a date — a problem so persistent that geneticists renamed the genes. Nothing of the sort happens here: values are shown exactly as the bytes in the file say they are.

CSV has no real standard, so the first job is working out what the file is. The delimiter may be a comma, a semicolon or a tab — European locales export semicolons, because the comma is their decimal separator — and the encoding may be UTF-8 or a legacy code page, which is what turns accented names into mojibake when guessed wrongly. Both are detected rather than assumed, which handles almost every real export, and both can be overridden when detection gets it wrong rather than leaving you stuck.

Quoting is where naive viewers fail. A quoted field can legally contain commas, line breaks and doubled quote marks, so a row holding a postal address spans several physical lines in the file while remaining one record. Anything that splits on commas mangles exactly those rows — which is why a file looks fine in a text editor and wrong in a viewer, or the reverse.

A ragged file — rows with more or fewer fields than the header — is shown as it is rather than being quietly padded or truncated, because a row with the wrong number of fields is usually the symptom of a quoting problem further up, and hiding it hides the bug. The whole file is parsed in the page, so memory use is proportional to its size.

When you want something else

At the command line, csvkit is built for exactly this: csvlook prints an aligned table, csvstat summarises every column, and csvclean reports and fixes the ragged rows. Miller — mlr --icsv --opprint cat data.csv — does the same at speed and handles files larger than memory. VisiData is the one to know about for exploring interactively: it opens a multi-gigabyte file instantly in a terminal, with sorting, filtering and frequency counts.

If you must open a CSV in Excel, use Data → From Text/CSV rather than double-clicking it. The import dialogue lets you set each column's type before anything is parsed, which is the only reliable way to stop the conversions above from happening. Double-clicking gives Excel permission to guess, and it guesses confidently.