A CSV of a few gigabytes is not a corrupted file, and the machine is not too slow. Three different limits are being hit at once, and they are easy to confuse:
| What you tried | What actually stopped you |
|---|---|
| double-click it, Excel opens | 1,048,576 rows and 16,384 columns per sheet; past that Excel truncates without asking |
| a script, pandas, a “big file” viewer | the file is loaded whole into memory — several times its size once it is parsed into rows |
| a text editor | it tries to lay out every line; the UI freezes before the file is even read |
The questions people ask about this are ten years old and still open: “How do I read a large csv file with pandas?” has 586,968 views on Stack Overflow (asked 2014-09-21), “Batch file to split .csv file” has 164,650 (2013-12-16), and the “split reading a large csv into evenly-sized chunks” question 51,037. On r/excel the same thing keeps coming back: “Excel file far too large to even open” (2026-04, 51 comments), “Having trouble extrapolating data from a 10GB CSV file” (2024-07, 32), “Excel File Grew from 400MB to 650MB” (2026-06, 77).
Two different jobs are hiding in that table, and they need two different answers: looking at the file, and handing the data on.
1. Opening it, without splitting anything
If what you need is to see what is on line 18 million, or how many rows match an IP, nothing has to be split and nothing has to be converted. BigTextFileViewer opens the file where it is and reads it in blocks instead of loading it:
The status bar is the whole claim: 1.097 GB, 26000001 Lines, 00:00:02 — the file was not converted, not split, and not loaded; it was opened. Search walks the file and reports matches with their line numbers, and jumping to a line number is instant, which is the difference between this and scrolling a 10 GB file in an editor.
2. Splitting, when the pieces are going somewhere
The moment the data has to leave — into Excel, into a database, into someone else’s inbox — the file has to become smaller than the limit that is blocking it, and splitting by rows is the cheap way to do it.
Reading a CSV means parsing: split the line on the delimiter, unquote the fields, convert numbers and dates, build a row object. That is where the memory goes — a 1 GB CSV can become 3–6 GB of objects in a Python process, which is exactly what the pandas thread is about.
Counting line breaks does not need any of that. A splitter can walk the file as bytes, note where every N-th line ends, and copy each block to its own file. Memory stays flat because nothing is parsed, and a quoted field containing a comma costs nothing.
The trade-off is stated in the same breath: a line-based splitter assumes one record per line. If a value contains a line break inside quotes, that record will be cut in half. Use it for the usual exports (no embedded newlines); for files where quoted line breaks are real, split with a CSV-aware tool that parses records first (DataFileSplitter does, and repeats the header in each part).
3. What a split looks like
BigTextFileSplitter is the line-based one: it never parses the CSV, never builds rows, and its Split Text File window asks for four things — the file, where the pieces go, how to split, and whether to repeat the header:
- Split By —
Lines(a row count per file),File Size(megabytes per file),File Count(how many
parts you want), or Symbol (a marker line, for log files).
- Max Lines per Split File — 500,000 is a good default for something that will be opened in Excel:
five parts of half a million rows each stay well under the row limit.
- Keep Header In Every File — the first line of the source is written at the top of every piece, so
each part is still a valid CSV with its column names.
The run itself is the whole point:
3,000,000 lines, 118.786 MB, 0.5 seconds, 6 files.
4. The numbers at a real size
The window above is 118 MB, which is a pleasant demo. A file that actually hurts:
| File | Rows | Result |
|---|---|---|
| 118.8 MB | 3,000,000 | 6 parts of 500,000 rows, 0.5 s, header in every part |
| 1.12 GB | 26,000,000 | 6 parts of 5,000,000 rows, 3.77 s, peak working set 51 MB |
The second line is the one worth reading twice: the memory does not follow the file. It was measured by polling the process while it ran, and the figure did not move as the file grew.
Two things were checked after that run, because they are the usual ways a split silently damages data:
- The line endings are copied, not normalised. A
LFfile staysLF; aCRLFfile staysCRLF.
(This was a real defect until this week — every piece used to come out CRLF.)
- Rejoining the parts reproduces the source byte for byte, once the repeated header lines are
removed: the MD5 of the reassembled file matches the original.
5. After the split
Each part is a normal file, so the destination decides what to do with it:
- into Excel — 500,000 rows per part opens without the “file too large” behaviour, and each part has
its own header row;
- into a database — FileToDB imports the parts as they are,
or a folder of them in one run;
- just to look — a part opens in any viewer, or in
BigTextFileViewer along with the rest of the file.
6. From the command line
“bat BigTextFileSplitter.exe split --from=big.csv --to=D:\parts --by=lines --max-lines=500000 “
For a nightly export the same command belongs in the scheduler; the GUI’s Save Task writes exactly that session.
FAQ
Why does Excel stop at 1,048,576 rows?
That is the sheet limit, and Excel does not warn you — it just stops. Splitting below that number (half a million is a safe round figure) is the only way to keep the whole file in spreadsheets.
Is splitting lossless?
Yes, as bytes: the pieces contain the original characters, the original line endings, and — with Keep Header In Every File — a copy of the header line. Concatenating the pieces (minus the repeated headers) reproduces the source exactly.
What about commas or quotes inside values?
A line-based split does not care: it never parses the fields, so commas and quotes are copied as they are. What it cannot do is keep a record that contains a line break inside quotes — that needs a CSV-aware split.
Can I split by size instead of by rows?
Split By → File Size cuts at a number of megabytes. Rows are usually the better unit when the parts will be opened in Excel; size is better for log files and for uploads with a hard limit.
Do I need to install anything on the machine that produces the file?
No. The splitter is a single desktop program; it reads the file where it is and writes the parts next to it.
Can it split a 20 GB file?
The limits are disk space and time, not memory: the splitter does not load the file. A 1.1 GB file takes under four seconds here, so the arithmetic is on your side.
Split it, then open it
Cut a file that will not open into parts that will — with the header repeated and every byte kept. Try BigTextFileSplitter — Windows / macOS / Linux, one-time price. To read a big file without splitting it at all, that is BigTextFileViewer.