How to Open a Large CSV File on a Mac

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 Apple does not say how much it can hold, 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, but Apple does not publish a maximum number of rows or columns, and the figures you will find on forums are not official. 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 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; we have not tested Safari, so we make no claim for it. They read the file from your disk in small pieces, so nothing is uploaded and a multi-gigabyte file needs no more memory than a small one:

• 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.
• Fix the encoding and delimiter when characters look garbled.

They do write their output to disk as they go, so you need roughly as much free space as the file itself takes.

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.