← All Kits · Excel Kit

How to Open a Large CSV Without Excel Falling Over

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

A CSV arrives, it is 1.8GB, and Excel either refuses it or takes nine minutes and truncates it. The instinct is to find a bigger spreadsheet. The better move is to stop trying to look at all of it at once, because almost every question you have can be answered from the first thousand rows plus one aggregate.

What you do, in order: peek, count, sample, aggregate. Only load the whole thing if a real question needs every row.

The short version. You need the columns and the size before you need the data.

Step 1: peek at the top

# Windows PowerShell
Get-Content big.csv -TotalCount 1000 | Set-Content sample.csv

# Git Bash, macOS, Linux
head -n 1000 big.csv > sample.csv

Both read only the beginning, so a 50GB file behaves the same as a 5MB one. Open sample.csv in Excel and you have the column names, the date format, the delimiter, whether the file is quoted, and whether IDs have leading zeros. That is most of what you needed.

Step 2: count the lines

# PowerShell. ReadCount batches the reads, which matters on big files.
(Get-Content big.csv -ReadCount 10000 | Measure-Object -Line).Lines

# Git Bash
wc -l big.csv

Write the number down. It is the only way to prove later that an import loaded everything. Note that a count of lines is not always a count of records: a field containing a newline inside quotes makes them differ, which is one more reason to check totals rather than trusting the eye.

Step 3: take a real sample, not the top

The first thousand rows are not a sample. They are the oldest thousand, or whatever the export sorted by, and any pattern you spot in them may be an artefact of that order. For a genuine look:

-- DuckDB
SELECT * FROM 'big.csv' USING SAMPLE 1000 ROWS;

# pandas, when the file fits in memory
df.sample(1000, random_state=0)

Step 4: aggregate, and bring back only the summary

DuckDB is the shortest route because there is no import step:

SELECT strftime(order_date, '%Y-%m') AS month,
       region,
       COUNT(*)   AS orders,
       SUM(amount) AS revenue
FROM 'big.csv'
GROUP BY 1, 2
ORDER BY 1, 2;

That turns millions of rows into a few dozen, which paste into Excel and behave. The setup is in setting up DuckDB.

The pandas equivalent, for a file that does not fit in memory, reads it in pieces:

import pandas as pd
totals = None
for chunk in pd.read_csv('big.csv', chunksize=500_000,
                         usecols=['order_date', 'region', 'amount']):
    g = chunk.groupby('region')['amount'].sum()
    totals = g if totals is None else totals.add(g, fill_value=0)
print(totals)

usecols is doing most of the work there. Reading three columns instead of forty is often the difference between fitting in memory and not.

The five tools, ranked by when to reach for them

On that last row: most editors load the whole file into memory and stop being useful in the hundreds of megabytes. Editors that stream, such as EmEditor or glogg, will open a multi-gigabyte file, and they are the right tool for exactly one job, which is looking at the row where an import failed.

The delimiter and quoting checks

1. Open the sample in a plain text editor, not in Excel, and look at the raw first line.
2. Count the delimiters in line 1 and in a few random lines. They should match. A line with more is an unquoted comma inside a field.
3. Look for a byte order mark: the first column header appearing as id means a UTF-8 BOM that some tools will treat as part of the name.
4. Check the line endings. A file written on Linux and opened in Notepad used to show as one long line. Anything modern handles it, but old importers still do not.

How to apply this to your own work

1. Put the peek command in a note somewhere you will find it. It is the single most used command on this page.
2. Count rows before and after every import, and keep both numbers in the workbook.
3. Install DuckDB the next time a file beats Excel, rather than the third time.
4. Ask the source system for an aggregated or filtered export. Half of all large-file work is unnecessary.
5. Never sample from the top when you are looking for data quality problems. Bad rows cluster, usually at the end.

The one habit to keep

Look at the file before you load the file. Two commands, ten seconds, and they prevent nearly every import surprise that costs an afternoon.

Do you know the row count of the last extract you built a report on?