A CSV looks like the simplest file format there is: one record per line, commas between fields. That mental model is exactly why so many CSV splits go wrong. Cut a real-world export "every 100,000 lines" and you'll often get parts with a broken last row, a part with no header, or a file where every accented name has turned into é.
This guide covers what a CSV actually is at the byte level, the four ways splitting breaks it, and how to choose a split that each downstream tool will accept. It's written from building the split.tools CSV splitter, so every rule here is one the code had to get right.
Why "one row per line" is wrong
RFC 4180, the closest thing CSV has to a standard, allows three characters inside a field as long as the field is wrapped in double quotes: the delimiter, the double quote itself (written twice, ""), and the line break. That last one is the trap. This is a valid two-row CSV with three physical lines:
id,note
1,"Call back Tuesday
re: invoice 2291"
Excel writes files like this whenever a cell contains Alt+Enter line breaks, and so do CRM exports with address or comment fields. A line-based tool such as Unix split -l sees three lines and can cut between line 2 and line 3. The first part then ends with an unterminated quote, and the second part starts with half a record. Both files are now invalid, and the damage usually surfaces later as an import error that names the wrong row.
The same issue makes wc -l lie: it counts physical lines, not records. A CSV with multi-line cells has more lines than rows.
How a correct splitter finds row boundaries
The fix needs no full CSV parser. A splitter only has to know one thing per byte: am I inside quotes right now?
- Start outside quotes.
- Every
"byte flips the state. An escaped quote""flips it twice, so it correctly cancels out. That's the elegant part of RFC 4180's escaping. - A line feed (
0x0A) seen outside quotes ends a row. A carriage return (0x0D) outside quotes ends one too, together with the line feed if one follows (CRLF), or on its own for classic Mac files.
Notice what's missing: the delimiter. Row boundaries depend only on quotes and line breaks, so comma, semicolon, tab and pipe files all split the same way. The delimiter only matters once you parse fields, which a splitter never needs to do.
The stray-quote failure mode
Quote tracking has one weakness: an unescaped quote inside an unquoted field, such as a product description 12" steel pipe written by a hand-rolled exporter. RFC 4180 forbids it, but it happens. The parity flips, the scanner thinks it's inside a field, and every line break until the next stray quote gets swallowed into one giant "row."
You can detect this cheaply: if the scan reaches end-of-file still inside quotes, the quotes are unbalanced. The split.tools splitter warns you when this happens. If your data has no genuine multi-line cells, switching quote tracking off recovers a correct line-based split. If it has both stray quotes and multi-line cells, the file is ambiguous and has to be fixed at the source.
Splitting bytes, not text, and why that protects your encoding
Most tools read the CSV into text, split it, and write the text back out. Every step of that round trip is a chance to change the bytes: a wrong encoding guess, normalized line endings, a dropped byte-order mark. The safer design is to never decode at all.
That works because of how UTF-8 is built. Every byte that matters to CSV structure (", ,, ;, tab, CR, LF) is ASCII, below 0x80. In UTF-8, every byte of a multi-byte character is 0x80 or higher: lead bytes run 0xC2–0xF4 and continuation bytes 0x80–0xBF. So a byte with the value 0x0A can only ever be a real line feed, never half of an "é" or a "日". A byte-level scanner is exact for UTF-8 without understanding UTF-8.
The same holds for ASCII, Latin-1 and Windows-1252, which are single-byte. It even holds for Shift_JIS, GBK and Big5, whose second bytes start at 0x40 and so never collide with quotes or line breaks. The encodings where it doesn't hold are UTF-16 and UTF-32. In UTF-16LE a line feed is the two bytes 0A 00, and an "ċ" (U+010A) contains the byte 0A too. Excel's "Unicode Text" export is UTF-16. It's recognisable by its FF FE byte-order mark, and the right move is to re-save it as "CSV UTF-8" before splitting.
The payoff is that every part is a byte-for-byte slice of the original, plus a copied header. Nothing is re-encoded, so nothing can be corrupted. It's also why the parts can be cheap to produce: in a browser, file.slice(start, end) refers to a byte range without copying it.
The byte-order mark: copy it into every part
Many UTF-8 CSVs begin with three invisible bytes, EF BB BF, the UTF-8 byte-order mark. UTF-8 doesn't need a BOM to define byte order, but Excel needs it to recognise the file as UTF-8 at all. When the BOM is missing, Excel on Windows decodes the file using the system's legacy code page, Windows-1252 in Western locales. The two UTF-8 bytes of "é" (C3 A9) then display as two characters: é.
That makes a naive split dangerous in a subtle way. Part 1 still starts with the BOM and opens perfectly. Parts 2 through 40 don't start with it, so they open with mojibake. A careful splitter copies the BOM to the start of every part.
There's one counter-case. Some parsers keep the BOM as part of the first column name, so your id column becomes id. In Python, read such files with encoding="utf-8-sig" (pandas accepts the same argument) and the BOM is stripped. If your parts are going only into code, you can drop the BOM. If any human will open them in Excel, keep it.
Line endings: keep what the file uses
CSV files in the wild end lines with CRLF (\r\n, the RFC's choice and Excel's default on Windows), LF (\n, Unix tools and most databases) or bare CR (\r, the old "CSV (Macintosh)" format from Excel for Mac). A splitter should recognise all three and preserve whichever the file uses. Silently converting CRLF to LF changes byte counts, breaks checksums against the source, and some strict importers reject the mismatch.
Mixed endings do happen, typically when two exports are concatenated. A byte scanner handles them naturally, because each terminator ends its own row.
Choosing how to split
| Goal | Split by | Typical setting |
|---|---|---|
| Open in Excel | Rows per file | 1,048,575 data rows (+1 header = Excel's 1,048,576 limit) |
| Google Sheets import | Rows per file | Sheets caps a spreadsheet at 10 million cells, so divide by your column count: a 25-column file fits ~400,000 rows |
| CRM / email-tool import with a row cap | Rows per file | The tool's documented limit, minus a margin |
| Upload portal with a file-size cap | Max size | The cap, measured including the header bytes in each part |
| Parallel processing | Number of files | One file per worker; equal row counts keep workers balanced |
Two details matter in practice:
- Rows means data rows. When the header is repeated, each part holds N data rows plus one header line. For Excel's limit, that's why the number is 1,048,575 and not 1,048,576.
- A size limit has to include the header and BOM. A 10 MB cap means header + data ≤ 10,000,000 bytes per part. A splitter that only measures data rows will overshoot by the header's length on every part.
Why big CSVs crash browser tools and spreadsheets
A 2 GB CSV defeats most tools before splitting even starts. Spreadsheets refuse it outright on row count. Upload-based websites need you to push 2 GB through your connection first: about 27 minutes at a 10 Mbps upstream. Browser tools that read the whole file into a JavaScript string hit a hard wall: V8, the engine in Chrome, Edge and Node, caps a string at roughly 2²⁹ characters (about 537 million). A 1 GB file can't be one string, and parsing it into an array of row objects multiplies memory use several times more.
The architecture that scales is streaming. Read the file in fixed slices (the split.tools splitter uses 8 MB), scan the bytes for row boundaries while carrying the inside-quotes flag from one slice to the next, and record only the offsets where parts begin and end. The parts are then assembled as references, a header plus a slice of the original file, so memory use stays roughly constant whether the file is 50 MB or 5 GB.
Doing it on the command line instead
If you live in a terminal, use a CSV-aware tool, not split:
- xsv / qsv:
xsv split -s 100000 outdir data.csvwrites 100,000-record chunks, quote-aware, with the header repeated in each. - Python:
pandas.read_csv(path, chunksize=100_000)yields DataFrames you can write out one by one. This re-serializes the data, so quoting style and number formatting can change. - Plain
split -l: fine only if you're certain there are no quoted line breaks. It doesn't repeat the header.
A checklist before you send the parts anywhere
- Row counts add up. The data rows across all parts should equal the source's record count, which is not its line count.
- Every part has the header, and it's byte-identical to the source header.
- Open the last part. It's the one most likely to be short or to hold the final row without a trailing newline (valid CSV, but worth seeing).
- Spot-check a multi-line cell near a part boundary if your data has them.
- Open one middle part in Excel and look at an accented name. If it shows
é, the BOM didn't make it into that part.
The split.tools CSV splitter shows per-part row counts and sizes before you download, warns on unbalanced quotes, and keeps the BOM and line endings exactly as they were. Your customer list or transaction export never leaves your machine in the process.