How to Analyse Large CSV Files Without Excel Crashing

Excel worksheets hold a maximum of 1,048,576 rows and 16,384 columns. If your CSV is bigger than that, Excel will load only the first rows and warn you, and even files well under the limit can make it slow or unresponsive. To analyse large CSV files you can load them into Power Query's data model, import them into a database, process them with a script, stream them in chunks, or work with a sample. Here is how to choose.

Why large CSV files break Excel

• The hard row limit. Anything beyond row 1,048,576 is not loaded into the sheet. If you do not notice the warning, your totals will be silently wrong.
• Memory. Excel holds the whole sheet in memory along with formatting and formulas. A 500 MB CSV can consume several times that once loaded.
• Volatile formulas. Thousands of lookups across hundreds of thousands of rows recalculate on every change.
• Type guessing. Excel may convert IDs to scientific notation, strip leading zeros from codes and reinterpret dates, which damages the data before you start.

Option 1: Power Query and the Data Model (stay in Excel)

Power Query (Data > Get Data > From Text/CSV) can load the file into Excel's Data Model rather than a worksheet. The data model is not bound by the worksheet row limit, and you can build PivotTables on top of it.

1. Choose Data > Get Data > From File > From Text/CSV and select your file.
2. Click Transform Data, set column types explicitly (text for IDs and codes).
3. Remove columns you do not need; this is the biggest performance gain.
4. Choose Close & Load To > Only Create Connection, and tick Add this data to the Data Model.
5. Insert a PivotTable from the data model.

Good for: a few million rows and people who already live in Excel. Weak for: very large files on a laptop with limited memory, and repeatable automated processing.

Option 2: Import into a database

Databases are built for this. SQLite (a single file, no server), PostgreSQL, MySQL or SQL Server can all import a CSV and answer questions over tens of millions of rows. Once loaded, one query replaces a morning of pivot tables:

SELECT region, SUM(amount) AS revenue FROM sales GROUP BY region ORDER BY revenue DESC;

If your data already lives in a database, skip the export entirely and report from it directly with a read-only user. Our guide to connecting SQL Server, PostgreSQL or MySQL safely walks through it.

Option 3: Stream it with a script

Streaming means reading the file a chunk at a time, updating running totals, and discarding each chunk before reading the next. Memory stays flat no matter how big the file is. In Python with pandas, pd.read_csv(path, chunksize=100_000) returns chunks you can aggregate in a loop. Command-line tools and libraries such as DuckDB or Polars can also query CSV files directly and efficiently.

Option 4: Sample sensibly

For exploring (not for final totals), a sample is often enough. If you want to know which columns are messy or what the typical order looks like, 50,000 random rows from a 5-million-row file will tell you. Rules for sampling:

• Sample randomly, not the first N rows. Files are often sorted by date, so the top rows are all from the same month.
• Never report totals from a sample unless you scale correctly and say so.
• Stratify when groups are small. If one branch has 2% of rows, a small random sample may miss it.

Option 5: Upload to a reporting tool that streams

Some reporting tools read files in a streaming fashion so they are not limited by spreadsheet row counts. Summarix, for example, accepts CSV and Excel files up to 25 MB and streams them, so exports with hundreds of thousands of rows work, and it computes the totals, KPIs and charts with code before writing the summary. For bigger datasets, connect the database directly instead. See features for details, or read turning a CSV into a report with AI.

Tips that help whichever method you choose

1. Drop unneeded columns before anything else. Twenty columns you never use can be most of the file.
2. Set column types deliberately. IDs, account numbers and product codes are text, not numbers.
3. Check the delimiter and encoding. Semicolon-separated files and non-UTF-8 encodings are common with South African exports from older systems.
4. Count rows before and after loading. If the file has 2,314,902 data rows, your tool should report the same.
5. Clean before you analyse. Our data cleaning checklist lists the 12 fixes worth doing first.

In short: stay in Excel with Power Query up to a few million rows, move to a database or script beyond that, sample only for exploration, and always verify row counts so the file you analysed is the file you think you analysed.