Monday, August 5, 2019

Opening Huge CSV Files

Matt Birchler:

Basically, BBEdit was remarkably fast at opening the file, being 4x faster than Visual Studio Code and 20x faster than Sublime. And then in terms of saving changes to the file, most apps were in the same ballpark, which I assume is because this is reliant on my SSD’s ability to save the data, not the app doing anything special.

Patrick Seemann:

ed: immediately
emacs: ~6s
vi: ~8s

I’d like to see the results for Excel, Numbers, and TextEdit.


Update (2019-08-06): Greg Hurrell:

Vim takes 8s to open a 1.5GB CSV file.

Matt Birchler:

I updated the post with the 3 apps you listed 🙂


Regarding the csv loading, Numbers can’t load more than 255 columns of data, which annoyed me frequently in the past few weeks. Google sheets and Excel had no problem with lots of columns.

Friedrich Markgraf:

Numbers does not support more than 65k lines. Current versions at least warn about that after the long import. Last year it just silently dropped lines.

7 Comments RSS · Twitter

FWIW, Querious has spreadsheety editing of CSV files in terms of rows and columns (with regex, splitting columns, joining columns, reordering etc) something like Numbers, but you know... actually works. Even on multi-gigabyte files. It's not blazing fast to open like raw text, but you get more out of it...

I'd be curious to see Hex Fiend tested.

Tried Hex Fiend on a synthetic 1.5GB CSV file, and opening was instantaneous.

Just tried it on, and it took ~230 seconds to open it… but it truncated to 65536 rows, which is the maximum number of rows for Numbers.

BBEdit remains an exemplar of software design and implementation at its finest. I've long been in the habit of opening large CSV files in BBEdit as a first choice for quick review instead of Excel, Numbers, or other tools. BBEdit remains the one app that always is open on my Mac, aside from a web browser.

I'll give honorable mention to another commercial text editor, UltraEdit by IDM Computer Solutions. It's not quite as elegant of a Mac app as BBEdit, but that's because it is truly cross-platform, with nearly identical Windows and Linux versions. I just tried dragging and dropping a 150 MB CSV file onto a few open applications, and the file felt like it snapped open in UltraEdit, possibly even beating BBEdit by a whisker. Impressive.

I just opened a 2.1GB csv in BBEdit, then did a search and extract for a common string using grep syntax. I didn't time it exactly, I think it took BBEdit a minute or so, but it did it just fine, and it's quick to navigate around both the original file and the new one created by the extract.

BBEdit. It doesn't suck.

Those numbers (65536 row limit, opening time) for Apple's Numbers are ridiculous.

I hate modern day Apple, where everything that doesn't lead to increased revenue in the next quarter (read iPhone) is done half-assed at best, if at done at all. :(

Leave a Comment