How to Open a Large CSV File on a Mac

tail -n +2 big.csv | split -l 1000000 - part_
for f in part_*; do
  { head -n 1 big.csv; cat "$f"; } > "$f.csv" && rm "$f"
done

To keep the file out of the grid, use head or less -S in Terminal to look inside it, and DuckDB to count, filter or total all of it.

A CSV with a few million rows is awkward on any computer, and a Mac adds a few twists: Numbers is the default spreadsheet but holds fewer rows per table than Excel, Excel for Mac lacks some of the import features of the Windows version, and commands copied from Linux guides do not always work in Terminal.

Here are the options, starting with the ones that need nothing installed. We tested the commands on a 99.4 MB export with 2,500,000 rows — in a Unix shell on Windows, not on a Mac, but using only options that Apple’s versions of the commands also have.

Excel for Mac

Excel for Mac has the same ceiling as Excel for Windows: 1,048,576 rows per sheet. Microsoft ties the limit to the file format itself — “the .xlsx file format has a limit of 1,048,576 rows per sheet” — so no version of Excel gets around it. A longer CSV is loaded up to that row, and the rows after it are not in the workbook. Do not save over the CSV at that point, or they are gone from the file too.

If the file fits, import it rather than double-clicking it, so Excel does not strip leading zeros or turn codes into dates. On Excel for Mac the importer is Data › Get Data (Power Query) › Text/CSV; converting a CSV to Excel without breaking it has the steps and the versions that include it. One feature Mac users will miss is From Folder, the Windows command for combining a folder of CSV files, which Microsoft does not list among the data sources for Excel for Mac.

Numbers

Numbers opens CSV files, and Apple’s Numbers features page gives its limit as 1,000,000 rows and 1,000 columns per table — the figure it states for importing and exporting Excel spreadsheets, and 48,576 rows fewer than Excel. The practical test is simple: note the row count before you open the file (see below), then check the last row in Numbers. If rows are missing, or Numbers becomes unresponsive, split the file into parts of under 1,000,000 rows and open those instead.

Look Inside Without Opening It: Terminal

Often you only need to see what is in the file — the column names, a few rows, the delimiter. Terminal commands handle that at any size, because they read the file as a stream instead of loading all of it:

head -n 20 big.csv
wc -l big.csv
less -S big.csv

• head -n 20 prints the first 20 lines: the header and, unless some values contain line breaks, the first 19 rows. It returns immediately, whatever the file size.
• wc -l counts line breaks, which is not quite the row count. On our test file it reported 2,525,775, while a CSV parser counted 2,500,000 rows plus the header: the difference is values with line breaks inside them. Counting the rows in a CSV explains how to get the exact figure.
• less -S lets you scroll through the file with the arrow keys, with long lines cut at the edge of the window instead of wrapped. Press q to quit.

To use them, open Terminal, type cd followed by a space, drag the folder containing the CSV from Finder into the window, and press Return.

Split It in Terminal

macOS includes split, but it is the BSD version, and it lacks options such as --additional-suffix that Linux guides often use. This version works on both, puts the header back on every part and names the parts part_aa.csv, part_ab.csv and so on:

tail -n +2 big.csv | split -l 1000000 - part_
for f in part_*; do
  { head -n 1 big.csv; cat "$f"; } > "$f.csv" && rm "$f"
done

Change 1000000 to the number of lines you want per part, and run it in a folder that has no other files starting with part_. It counts lines, not rows, so if your data has line breaks inside values it can cut a row in half; splitting a CSV from the command line shows when that happens and a script that avoids it.

Query It with DuckDB

When you need answers from the data rather than a spreadsheet of it — totals, counts, the rows for one store — DuckDB is the most capable free option. It is a single program that runs SQL directly on CSV files. Install it with brew install duckdb if you use Homebrew, or with the installer command on duckdb.org, then type duckdb in Terminal and run queries such as:

SELECT count(*) FROM 'big.csv';
SELECT * FROM 'big.csv' LIMIT 20;
COPY (SELECT * FROM read_csv('big.csv', all_varchar = true) WHERE store = 'Paris') TO 'paris.csv';

The first counts rows properly, line breaks inside values included; on our file it returned 2,500,000. The second shows the first 20 rows as a table. The third writes every row for one store to a new CSV that is small enough to open in a spreadsheet — 62,500 rows in our test. all_varchar = true keeps each value exactly as it was: without it, DuckDB read amount as a number and wrote 0 back out as 0.0.

In the Browser, with Nothing Installed

The tools on this site run in the browser, so there is nothing to install. We test them in current versions of Chrome, Edge and Firefox on Windows. We have not tested them on a Mac in any browser, Safari included, so we make no claim for a Mac. They read the file from your disk in small pieces, so nothing is uploaded, and splitting or converting a multi-gigabyte file needs no more memory than a small one:

• Page through and search the file without loading it, jumping to any row number, as an alternative to less -S that shows proper columns.
• Count the rows and profile the columns, which reports the physical line count and the CSV record count side by side, so the gap between them and wc -l stops being a mystery.
• Split a large CSV by rows, by maximum size or into a set number of files, without ever cutting a row in half.
• Split by column value to get one smaller file per store, region or client.
• Remove duplicate rows and see how many there were. This one keeps a small fingerprint of every different row in memory, so it stops at about 33 million different rows.
• Fix the encoding and delimiter when characters look garbled.

The first two only read; they write nothing. The ones that produce files write their output to disk as they go, so for those you need roughly as much free space as the file itself takes, or about twice that when splitting, while the parts and the ZIP both exist.

Which to Use

For more on each route, including Excel’s Data Model on Windows, see opening a CSV that is too large for Excel.