A Large CSV File - What Three Failed Attempts Taught Me
Everyone does the same thing when a large CSV file arrives. Double-click. Then wait. Once a settlement export clears 100MB, Excel drops into "not responding" and stays there.
What comes next is the interesting part. Anyone wrestling with a file like this tends to get stuck three times, and each failure teaches something different. Only at the third does it become clear that the approach itself was wrong from the start.
- The distinct cause behind each of the three symptoms.
- Why opening and saving is dangerous, with the evidence.
- How to switch from opening files to extracting from them.
Failure one: it opens, but you cannot work in it
Wait a few minutes and a large CSV file does open. That is where the trouble starts. A single Ctrl+F stalls for another age, and with dozens of columns you scroll sideways forever hunting for the one you want.
The usual conclusion here is "my machine is slow," but the cause is not the hardware. Excel is a tool that puts the entire file on screen, so slowing down as the file grows is what it is built to do, and better specs only flatten the curve slightly.
What you needed was three or four columns and a few thousand matching rows, and instead you loaded a million rows and went wandering through them.
Failure two: the data is cut off in the middle
This is the dangerous one. The first is merely inconvenient; this one can pass unnoticed.
An Excel worksheet has fixed limits. Per Microsoft's official documentation that is 1,048,576 rows by 16,384 columns, with the older xls format stopping at 65,536 rows. A CSV file is just text and has no row limit at all, so files that exceed the ceiling circulate routinely in day-to-day work.
| Format | Maximum rows displayed |
|---|---|
| Excel xls (legacy) | 65,536 rows |
| Excel xlsx (current) | 1,048,576 rows |
| A CSV file itself | No limit (plain text) |
Open a file past the limit and only the first 1,048,576 rows load; the rest are simply not there. The screen, however, looks perfectly normal. Save without thinking and you have a file missing everything that was cut, and if you overwrote the original there is no way back.
The data is still intact in the original CSV. What got truncated was only the copy loaded onto the sheet. So the rule is simple. Do not open and save a large file; take what you need out of the original. If you need to inspect it, work on a copy.
Failure three: the text turns to nonsense
You finally get it open and every name and address is mangled, so you assume the file broke and download it again. It comes back the same.
The file is not damaged; the characters are being interpreted with the wrong scheme. Korean environments mix UTF-8 and the EUC-KR family, so opening a file under a standard other than the one it was saved in makes text look broken. The original is fine and only the reading side needs changing.
There is one more trap here. Save it while it looks broken and that is when it genuinely breaks. If the screen looks wrong, check the encoding before saving anything.
What only becomes visible after the third failure
Line the three up and the common thread appears. Every one of them arises from trying to open a large CSV file whole. The slowness, the truncation, and the mangled text all happen during the act of opening.
So change direction. Instead of opening it, select the rows matching a condition or just the columns you need and take them as a new file. However large the original, the result is small, and nothing gets truncated.
Power Query and Python both do this, though neither is quick to learn. For urgent work, dropping a large CSV file into Large CSV Data Extraction is faster.
It detects the encoding automatically and handles up to 250MB on desktop. Mobile browsers have far less memory available, so there the limit is 30MB — open big files on a desktop.
Name a condition column and a value and it pulls out only the matching rows: the orders containing a particular product code, the settlement lines for one merchant. Leave the value blank and every row qualifies, which makes it a way to count how many there are.
Pick the ones you want from dozens and take a file containing only those. Order number, amount, and date pulled out for a report is the classic case. It can also extract unique values with duplicates stripped, which helps when you are working out what a column actually contains.
If the file holds customer data
Order and membership data often carry names, phone numbers, and addresses. If a service uploads the file to a server for processing, that data is leaving your control, so check how it works before uploading.
Whether processing happens entirely inside the browser is the test. The extraction tool described here never sends the file out of the browser.
If the result has to be submitted as paperwork, Merge PDF will bundle it with your other evidence into one file. The order to arrange documents in is covered in the guide to merging supporting documents.
In one line
What we actually wanted from a large CSV file was never "open the file" but a few rows and columns inside it. Stop trying to open it, take only what you need, and all three failures disappear at once.
If there is a file on your desk that will not open, put it into Large CSV Data Extraction.
References
- Microsoft, "Excel specifications and limits": worksheets hold a maximum of 1,048,576 rows by 16,384 columns. View the documentation
- Microsoft, "What to do if a data set is too large for the Excel grid": guidance on data exceeding the grid. View the documentation