Guide

How to open a large CSV file on a Mac

A spreadsheet stops somewhere between a million rows and the moment your Mac runs out of patience. The file does not care: an export is an export. Here is what the tools on your machine can do with a very large CSV, where each of them stops, and how to read and edit one without loading it into anything.

Where the usual tools stop

Excel holds 1,048,576 rows and 16,384 columns, and a file with more is truncated on import — quietly enough that people have shipped analyses of the first million rows of a three-million-row export. Numbers is more comfortable to use and gives up sooner. Both of them read the whole file into memory before they show you anything, which is why a 2 GB export turns into a spinning beachball rather than an error message.

The command line does better and asks more of you. less exports.csv opens instantly and shows you the raw commas. head -n 100 and awk -F, '{print $3}' will answer a specific question about a specific column. csvkit and q will run SQL over the file. None of them lets you see the table, find the three rows that are wrong, fix them and save.

The four things that actually go wrong

The separator is not a comma. A .csv written by a German, French or Swiss spreadsheet is separated by semicolons about as often as by commas, because the comma is the decimal point. A reader that assumes commas shows you one column.

The file is not UTF-8. An export out of an older system is Windows-1252, or Latin-1, or an EBCDIC extract that came off a mainframe. Opened as UTF-8 it is full of question marks and every name with an accent in it is damaged — and saving then makes the damage permanent.

A field contains the separator. "Zurich, Switzerland" is one field, and a naive split turns every row after it into nonsense. So does a field with a line break in it, which is legal and common in address and comment columns.

The file is bigger than the machine. Not rare: a database dump or an event export is routinely several gigabytes, and anything that reads it whole needs that much memory plus whatever the program adds on top.

Reading a big CSV in Volt

Volt opens a file of any size in about the same time — a 4.23 GB log of 50.4 million lines takes two milliseconds — because past a size you set it does not read the file to open it. A CSV of a few gigabytes is open before your hand leaves the mouse, and scrolling, searching and jumping to a line are the same speed at row 40,000,000 as at row 40.

CSV and TSV have a mode of their own, so the fields are coloured and the one your caret is in is obvious — which is the thing counting commas is for. ⌘R opens the preview beside the text and renders the file as the table it is: the separator read from the file rather than from its name, long fields wrapped inside their column, and the line breaks inside a field kept, because that is what tells a broken row from a long one. The table is built for size, so a million-row file previews as a table rather than as a wait.

The character set is detected when the file opens and the status bar says which one it chose. If it chose wrong, pick another and Volt asks the question that matters: re-read the same bytes in that set, or convert the text to it. Saving keeps the encoding, the line endings and the final newline the file arrived with, so a file that came from a mainframe goes back looking like one.

Working on one

• Only the rows that matter. Find ▸ Show Only Matching Lines (⌥⌘L) hides every row that does not match — grep, still in the window, with the rows editable and their real line numbers intact. Filter Lines… takes several conditions at once.
• Count what is in a column. Find ▸ Tally Matches counts every match of a search by what it says, most frequent first, in a new document: one command instead of cut | sort | uniq -c | sort -rn.
• Add up a column. Select the figures and the status bar says the sum, how many numbers and the average.
• Edit a column. ⌥-drag makes a rectangular block down the rows; typing, deleting and pasting happen at every cursor at once, as one undo step.
• Sort and de-duplicate. Sort in natural order — row 10 after row 9 — reverse, remove duplicate rows or only consecutive ones.
• Go to a row. ⌘L takes 412 for the row and 412:5 for the row and column.
• Fixed-width, not separated at all? Give Volt the field widths the way a copybook writes them and it rules a hairline down each boundary — then export the record file as a real CSV.

A compressed export

An export usually arrives as .csv.gz. Volt opens it as its text and saves it back compressed, and the same is true of .bz2, .zst and .xz — so there is no unpacking step, no temporary copy on the disk and nothing to tidy up afterwards.

Questions

Related

• Opening large files on a Mac — what less, tail and split do, and where they stop.
• Reading a log file on a Mac — colouring, filters and jumping to a time.
• Editing a .gz without unpacking it — gzip, bzip2, Zstandard and xz.
• Every file Volt opens — languages, character sets, records.