As a data analyst in a large organization, we still deal with large Excel files - wondering what the HN analytics community uses to open these large files?
If you are confident that it is well-formed, bulk-load it into SQLite is the easiest method. There're methods in R and Pandas too.
Or you could load into the Excel data model instead of the grid, look in the Get & Transform feature. Then do your initial exploration there. Data model (formerly Power Pivot ) is very underrated IMHO.
I'd say several million to several hundred million rows. Haven't seen a CSV that has a billion rows yet although I have had to consolidate multiple million row files into a single CSV.
I use the sql BULK INSERT command to put it in a table. It's simple, fast, and gives you options to generate error files containing any rows with malformed data.
If you can do what ever you want to do by analyzing one line at a time I would recommend reading the file as a stream for processing.
If you can't do whatever you'd like one line at a time, read the file as a stream and write it to disk with some database(e.g sqlite, MySQL, Mongo). This will be slower but might be easier to play with the data.
Targeting .NET and SQL Server, the LumenWorks CsvReader in conjunction with the SqlBulkCopy class. The performance quite good and avoids issues with permissions and file access when attempting to automate bcp commands.
17 comments
[ 3.0 ms ] story [ 21.0 ms ] threadOr you could load into the Excel data model instead of the grid, look in the Get & Transform feature. Then do your initial exploration there. Data model (formerly Power Pivot ) is very underrated IMHO.
What I do is dump it as CSV again after manipulation and then bulk load it into the DB using the DB's native tool. It's messy but 10x faster.
If you can't do whatever you'd like one line at a time, read the file as a stream and write it to disk with some database(e.g sqlite, MySQL, Mongo). This will be slower but might be easier to play with the data.