How to open a CSV file that’s too big for Excel (more than 1,048,576 rows)

On the command line, wc -l data.csv (macOS and Linux) or find /c /v "" data.csv (Windows Command Prompt) gives the same answer. Remember that the count includes the header line, and that a value with a line break inside quotes spans more than one line. Our CSV row counter page explains the difference between lines and records.

Option 1: Split the CSV into smaller files

If you need the data in Excel itself, split the file into parts that each fit under the limit, with the header row repeated at the top of every part. At 1,000,000 data rows per file, a 3.4-million-row CSV becomes four files. On macOS or Linux (or on Windows through WSL or Git Bash):

# Keep the header, split the rest into parts of 1,000,000 lines
tail -n +2 data.csv | split -l 1000000 - part_
for f in part_*; do
  { head -n 1 data.csv; cat "$f"; } > "$f.csv" && rm "$f"
done

A MegaRows CSV splitter that repeats the header and respects quoted line breaks is coming soon.

Option 2: Power Query and the Data Model

Excel can work with far more rows than the grid shows, as long as you keep them out of the grid. Power Query loads the CSV into the Data Model, a compressed in-memory database inside the workbook, and PivotTables and PivotCharts then summarise all of the rows. In Excel for Windows:

1. Go to Data › Get Data › From File › From Text/CSV and choose the file.
2. In the preview window, click the arrow next to Load and choose Load To…
3. Select Only Create Connection, tick Add this data to the Data Model and click OK.
4. Use Insert › PivotTable › From Data Model to summarise the full data set.

Power Query can also filter and clean the data before it loads. If you only need last year’s orders or a handful of columns, the result may fit in the grid after all. Power BI Desktop, a free Windows app, uses the same engine.

Option 3: Load it into a database

Databases are built for millions of rows. Two free options need no server and install in a minute: DuckDB, which queries CSV files directly, and SQLite, which imports them into a single database file.

-- DuckDB: query the CSV directly, no import step
SELECT count(*) FROM 'data.csv';
SELECT region, sum(amount) FROM 'data.csv' GROUP BY region;

-- SQLite (in the sqlite3 shell): import once, then query
.import --csv data.csv sales
SELECT count(*) FROM sales;

Graphical tools such as DBeaver or DB Browser for SQLite give you a spreadsheet-like view of query results.

Option 4: Python and pandas

If you are comfortable with a little code, pandas reads a CSV of a few million rows in seconds:

import pandas as pd

df = pd.read_csv("data.csv")
print(len(df))          # data rows, header excluded
print(df.head(20))      # first 20 rows
df[df["region"] == "North"].to_excel("north.xlsx", index=False)  # needs openpyxl

If the file is bigger than your memory, process it in chunks:

total = 0
for chunk in pd.read_csv("data.csv", chunksize=1_000_000):
    total += chunk["amount"].sum()

Polars is a faster alternative with a similar feel, and its lazy scan_csv handles files larger than memory.

Option 5: Command-line tools

For a quick look, the tools built into macOS and Linux (and available on Windows through WSL or Git Bash) are hard to beat:

head -n 20 data.csv          # first 20 lines
tail -n 20 data.csv          # last 20 lines
wc -l data.csv               # count lines
grep -c "London" data.csv    # count lines that contain a word

Dedicated CSV tools such as xsv, qsv and csvkit understand quoting and can select columns, sort, filter and compute statistics on files of many gigabytes.

Option 6: Browser-based tools

Online CSV viewers need no installation, but check two things before dropping a large file into one. Many upload your file to their server, which is slow for big files and a problem for confidential data. And many cap the file size or row count well below what you need.

MegaRows takes a different approach: everything runs locally in your browser, so the file never leaves your device. Today it offers the row counter you used above. A viewer for files with millions of rows is coming soon, along with charts, converters and split, merge and compare tools.

Which option should you choose?

If you only need to know how big the file is, count it first. If colleagues need the data in Excel, filter it down with Power Query or split it. If you work with files like this every week, a little DuckDB or pandas pays off quickly.