Menu
Are there any good programs for dealing with reading large CSV files? Some of the datafiles I deal with are in the 1 GB range. They have too many lines for Excel to even deal with. Using Access can be a little slow, as you have to actually import them into a database to work with them directly. Is there a program that can open large CSV files and give you a simple spreadsheet layout to help you easily and quickly scan through the data?
400
csv files have a limit of 32,767 characters per cell. Excel has a limit of 1,048,576 rows and 16,384 columns per sheet. CSV files can hold many more rows. You can read more about these limits and others from this Microsoft support article here.
Probably the most commonly used program for opening a CSV. Here, you'll encounter a 1,048,576 row limit. You'll most likely receive a notification if your file exceeds this, so you'll be warned that you aren't viewing all data. Similar to Excel, with Mac Numbers you'll see warning if you'r file exceeds 1,000,000 rows.
You may generate large csv file yourself manually - save about million of rows from excel as csv, open such csv in Notepad, copy and add or add manually another half millions of rows or so, close the file. Now try to open in Excel - it will show only first million.
Right-click on the Excel XLS/CSV file and select "Properties"; 2. Go to Previous Versions, find and select the latest version or the last version before Excel replaced or saved, click "Restore". Then you can get your Excel file XLS or CSV restored without losing any saved data.
MySQL can import CSV files very quickly onto tables using the LOAD DATA INFILE command. It can also read from CSV files directly, bypassing any import procedures, by using the CSV storage engine.
Importing it onto native tables with LOAD DATA INFILE has a start up cost, but after that you can INSERT/UPDATE much faster, as well as index fields. Using the CSV storage engine is almost instantaneous at first, but only sequential scan will be fast.
Update: This article (scroll down to the section titled Instant Data Loads) talks about using both approaches to loading CSV data onto MySQL, and gives examples.
186
I've found reCSVeditor is a great program for editing large CSV files. It's ideal for stripping out unnecessary columns. I've used it for files 1,000,000 record files quite easily.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
