How to Import a Large CSV file to MySQL
One of my clients recently needed over 100,000 lines of a CSV file added to a database. At first being a PHP developer I tried making an upload page and looping the results through fgetcsv() but this didn’t end up being the most efficient solution. There is a much faster way to do this… put your CSV directly into MySQL.
1. Create the table you will store the items in.
Using the labels in your CSV file that are normally on line 1 (using the head fields makes this really easy). You’re probably looking at something like this:
ID,Name, Phone, Address 1,Philip Newman,0210483999,20 Paul St. 2,James Newman,0210483998,10a Ben Lora Rd. 3,Jason Davis,021048392,11 Sady Cres. ...
The csv file is set up with four columns.The first step is to create a table in a mysql database that has the three columns as fields in the table. I’m not going to show the specifics, but if you don’t know how to create a table in mysql I’ll write a tutorial later. Remember if your database is big, be sure to put the index/primary/unique keys on before you upload the database.
2. Create a simple mysql query.
Now comes the magic, here’s the query to insert your file.
LOAD DATA INFILE '/tmp/your_file.csv' INTO TABLE your_temp_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES (ID, Name, Phone, Address);
LOAD DATA INFILE loads the file. Make sure you use the full system path to the file here not relative to your home directory.
INTO TABLE is the table you’re like your data to go into. Make sure you’re table is setup before you run this command.
IGNORE 1 LINES skips the first line normally you’re table headers in the CSV file.
For more information on the LOAD DATA query take a look at it’s reference page.
This might not be the most efficient solution but let me know if it helps you out.