Splitting .csv files that exceed Excel's row limit

  • Posted on: 29 May 2015
  • By: davis

Best tool for the job I've found: Open Source CSV File Splitter (OSV)

Very quick and easy to use. Specify lines per chunk file and you're good to go.

Keep in mind many office suites (OpenOffice/LibreOffice) have row limits around 65k (pre 3.3.3: limit is 65,536 rows | 3.3.3 and later: 1,048,576 rows)

In Excel 2010, the maximum worksheet size is 1,048,576 rows.

Honorable mention: HeidiSQL, which I really wanted to use, but errors threw constantly on my import attempts. It could be my novice mistakes, or the tool just didn't work. It's certainly promising.

Nearly forgot to mention: to easily set up column headers in MySQL, I used http://www.convertcsv.com/csv-to-sql.htm, which is a lovely tool. Pre-writes your SQL statements for you. Grabbed the headers from the (too-large) .csv, made a file with only those headers, imported into this tool, and then set up a new test schema/table.

If the link for OSV goes down: mirror (hosted by me)