How to split a large CSV file into smaller files

An Excel worksheet holds 1,048,576 rows. Open a bigger CSV and Excel loads only that many, warning "This data set is too large for the Excel grid. If you save this workbook, you'll lose data that wasn't loaded." Upload tools have caps too: ChatGPT takes CSV and spreadsheet files up to about 50 MB.

Each chunk is one SQL query. Ordering by the hidden __rid column, the row number from the file, keeps the original order:

SELECT * FROM data ORDER BY __rid LIMIT 500000 OFFSET 0

1. Open https://rows.page and drop your CSV or TSV file on the page.
2. Type the query above in the search box and press Enter.
3. Open the Export menu and choose CSV to download the first chunk.
4. Raise the offset by 500,000 (OFFSET 500000, then OFFSET 1000000) and export again until a query returns fewer than 500,000 rows.

SQL results stop at 1,000,000 rows, so keep LIMIT at 1000000 or below. Every CSV export has the header row, commas and UTF-8 without a BOM, and the __rid column is left out. Text cells that start with =, +, - or @ get a leading apostrophe so spreadsheets don't run them as formulas.

To pull out the rows for one country, status or customer, use a filter instead of SQL. A filtered view exports every matching row, even past 1,000,000, with only the columns you have visible.

1. Drop your file on https://rows.page.
2. Type a filter in the search box, such as country:NL, country:NL OR country:BE or country:NL price:>20, or click a top value or histogram bar in the Columns panel.
3. Hide any columns you don't want in the output.
4. Open the Export menu and choose CSV, Parquet, JSON or JSONL.

To hand the subset to someone, click Share. It uploads the whole file, and the link opens with your filter applied, so the other person can still clear it and see every row.

GNU split

For files over 1.5 GB, or to write every chunk in one go, use split. It treats the CSV as plain lines, so the header ends up in the first file only:

split -l 500000 big.csv part_

That writes part_aa, part_ab and so on, 500,000 lines each. Because split counts line breaks, not CSV records, a quoted field with a line break inside it can be cut in half.

Keep the header in every part

Save the header, split the rest, then put the header back on each part with a .csv extension:

head -n 1 big.csv > header.csv
tail -n +2 big.csv | split -l 500000 - part_
for f in part_*; do cat header.csv "$f" > "$f.csv" && rm "$f"; done

DuckDB: one file per value

To write a separate file for every value in a column, run PARTITION_BY in the DuckDB command-line tool. DuckDB parses the CSV properly, including line breaks inside quoted fields:

COPY (SELECT * FROM 'big.csv') TO 'out' (FORMAT csv, HEADER, PARTITION_BY (country), WRITE_PARTITION_COLUMNS true);

You get one folder per value, such as out/country=NL/data_0.csv, each file with its own header. Without WRITE_PARTITION_COLUMNS true, DuckDB leaves the country column out of the files and keeps it only in the folder name.