Open a CSV file bigger than Excel's 1,048,576 rows
Excel's grid ends at 1,048,576 rows. With a bigger CSV it warns that the data set is too large for the grid, and the rows past the limit are lost if you save. Exports of logs, orders or database tables are often bigger than that.
BigText opens a CSV of any size as a table at once and never loads it into memory, so a file with 50 million rows scrolls as fast as one with fifty. It is free and runs on Windows, Linux and macOS.
Step by step
Open the file
Download BigText and open the .csv or .tsv with File → Open, or drag it onto the window. It opens as a table: the delimiter (comma, semicolon, tab or pipe) is found from the first lines and the first row becomes a pinned header.
Ctrl+Tswitches between the table and the text.Find your way around
Scroll, press
Ctrl+Gto go to a row, orCtrl+Fto search the whole file; Find All lists every match with its line. Nothing waits for the file to load, because it is never loaded.Analyse it without Excel
Right-click a column header: Statistics… (rows, empty and distinct values, sum, mean, min, max and the most frequent values), Filter rows by this column, Group rows by this column… with sums of another column, and Pivot by this column…. Sorting works on files up to 1 GB.
Or make it fit Excel
File → Split file… cuts the CSV into
name.part001.csv,name.part002.csv… of a given number of lines, say 1,000,000, always between records (a quoted field with line breaks stays whole), and Repeat the header row in every part puts the column names at the top of each one. To keep only some rows, filter them (Filter rows by conditions…) and save the result as a new, smaller CSV.
Good to know
- View → Check table rows… lists the records with the wrong number of fields, a stray quote or text in a number column, with their line numbers.
- The same tools work from scripts with
bigtext-cli, for examplebigtext-cli pivot.