Or how to handle a large CSV in Microsoft Excel or Google Sheets?
In this day and age, having a CSV file larger than 1 million rows is surprisingly common.
Microsoft Excel famously limits the number of rows in a CSV file where you can't import more than 1,048,576 rows. Google Sheets has a similar limitation where you can't import more than 10 million cells.
These limitations are hard limitation, which means there is no way around it unless you switch to a different tool, use a separate app to split the CSV file, or change your workflow drastically.
We don't recommend splitting a CSV file and use multiple sheets on Excel. It'd be really slow for Excel to process that much information. Operating on multiple sheets (e.g. combining pivot tables) is tricky and error-prone. We recommend using a new tool and changing your workflow instead.
Here are 4 ways to handle large CSV files with pros and cons:
1. Use PowerPivot in Excel
You can load your CSV file into a "Data Model" and use PowerPivot to perform a pivot operation on the Data Model.
Jose Barreto, a Microsoft employee, wrote a great tutorial here: Loading CSV/text files with more than a million rows into Excel.
You are using only one app, which is Excel.
PowerPivot's skill is not transferable, and it still imposes the 2GB file limit.
2. Use Microsoft Access
You can also use Microsoft Access, which is a database tool that enables you to import CSV files and write SQL.
The feature set of Microsoft Access is very rich, so it might be wise to look through some tutorials even if you already know SQL well. Here's a recommended tutorial from GCFGlobal: Getting Started with Access
Microsoft Access might already bundled with your Microsoft Office package, so it is free.
Access is a database tool, which can be cumbersome to use. Also, you will need to learn SQL.
3. Use a command-line tool
There are myriad command-line tools that enable you to work with large CSV files like:
These tools are often free and offer powerful functionalities.
Using a command-line tool to explore and analyze CSV files is clunky. You also might need to learn a programming language.
4. Use Superintendent.app
I've tried using the 3 above options in my work and found them to be painful to use. Eventually, I built superintendent.app to solve my own pain point. Since then, it has served thousands of users and made their work life more pleasant.
Superintendent.app is a desktop application, is fast, and can handle large CSV files easily. You can get started in seconds: install, add CSV files, and write SQL.
You can scroll through millions of rows, sort, switch between different sheets in order to build good intuition about your data. You can build a graph and export your results back to CSV files.
We offer an unlimited free trial period. I'd love for you to try it out. I hope it'll make your work life more pleasant as it has made mine.
Learn more about Superintendent.app →
Light-weighted. Low learning curve. No file size limit. Easy to use. You can get started in seconds.
You will need to learn SQL.
