Fill MySQL Table With Data From CSV
It’s probably been in every PHP ‘test’ going and is a very commonly asked question, but it’s something I’ve never covered here at Return True before. So what exactly is the best way to insert data into a MySQL table from a CSV file? Well I can’t say these are the best ways, but here are two ways I’ve come up with that are pretty simple and don’t require a huge amount of time either.
N.B To keep both examples short & sweet I have omitted error checking for MySQL connections and any validation. While you will need to add in MySQL connection error checking you may not need to add validation depending on if you implicitly trust your CSV source or not. If in doubt, it is always best to validate though.
Both of these examples use a sample CSV that has a CSV header (the column names as the first line) and the data it contains looks like this:
|
1 2 3 4 5 |
Gender,GivenName,Surname,City,Centimeters female,Saho,Shini,EASTON,166 male,Chuu,Umeta,"GOOD EASTER",180 male,Naoya,Umeta,RUDHEATH,177 female,Sawako,Sugimoto,"CROSS INN",172 |
My MySQL table’s schema looks like this:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE `people` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `gender` varchar(255) NOT NULL, `city` varchar(255) NOT NULL, `height` int(4) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
Using MySQLi With fgetcsv
The first method will just use built in PHP functions and the MySQLi extension. This is great if you only have access to your base PHP install and no access to package installers like Composer. The big caveat is that using fgetcsv can be a little less intuitive than the interface some CSV packages provide.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
$mysqli = new mysqli('localhost', 'username', 'password', 'database'); //Connection Error checking goes here $stmt = $mysqli->prepare("INSERT INTO people (name, gender, city, height) VALUES (?, ?, ?, ?)"); $path = 'path/to/csv.csv'; if(!file_exists($path)) die('No file!'); $h = fopen($path, "r"); //Counter to skip first row, Only needed if CSV includes headers on first row $c = -1; while ($data = fgetcsv($h, 1000, ',', '"')) { $c++; if($c == 0) continue; if(current($data)) { $gender = $data[0]; $name = $data[1] . ' ' . $data[2]; $city = $data[3]; $height = $data[4]; $stmt->bind_param('sssi', $name, $gender, $city, $height); $stmt->execute(); } } $stmt->close(); $mysqli->close(); |
Okay. So this whole thing is pretty simple. Let’s run down what we do:
- Connect to the database with MySQLi
- Create our prepared statement
- Setup our CSV path and check the file exists
- Create a
fopen()stream of the file and assign it to the variable$h - To be able to skip the first row (which usually contains the CSV headers) we set a counter variable to -1
- We use
fgetcsv()to run through each line of data in the CSV file. More on this function in a moment - We increment our counter first, then if we are at 0 (because our count started at -1) this is the first line, so skip it. If your CSV has no header line then remove this
- If
current()gives valid data then we can run our query. - I have set variables to show which column contains what data, but you could just use the array directly in
bind_param()if you wished. - We bind parameters and execute.
It is important to note that when using fgetcsv() you need to set the parameters based on your CSV. My example CSV uses commas as delimiters (separator) and double quotes as enclosures (holds strings with spaces). Also note that my CSV holds the name as first & last name. My table however only has a single name field so I have merged the two items into a single name variable.
That’s about it. Now let’s move onto our second version.
Using PDO with League/Csv
This time we will be using PDO to connect to our database and the excellent CSV package from The League of PHP. To do this you will need Composer which is why it is an optional version as sometimes using Composer just isn’t an option.
To get started pull in the CSV package using composer by running:
|
1 |
composer require league/csv |
Then in your script you will need to pull in the composer autoload file and use the correct namespace. I’ve added both these in the code so you can see them.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
use League\Csv\Reader; //require composer autoload require 'vendor/autoload.php'; //Connect to MySQL with PDO $db = new PDO("mysql:host=localhost;dbname=database", "user", "password"); //Check for connection errors $stmt = $db->prepare("INSERT INTO people (name, gender, city, height) VALUES (:name, :gender, :city, :height)"); $path = 'path/to/csv.csv'; if(!file_exists($path)) die('No File!'); $csv = Reader::createFromPath($path); $csv->setOffset(1); $blkInsert = $csv->each(function ($row) use (&$stmt) { if(!current($row)) return false; $stmt->bindValue(':name', $row[1] . ' ' . $row[2], PDO::PARAM_STR); $stmt->bindValue(':gender', $row[0], PDO::PARAM_STR); $stmt->bindValue(':city', $row[3], PDO::PARAM_STR); $stmt->bindValue(':height', $row[4], PDO::PARAM_INT); return $stmt->execute(); }); $dbh = null; |
Again let’s go through things quickly.
- We use the namespace required for the CSV reader.
- We connect to the database using PDO
- We prepare our MySQL statement
- We create our CSV reader from our CSV file
- We offset by 1 row since I have a header line in my CSV, remove if you don’t
- Check to see if the current row has data, return false to exit the
eachif it doesnt. Prevents ‘Notice’ errors due to accessing non-existing array items when running out of rows if your CSV has an empty line. - Use the
eachmethod on the CSV reader to bind & execute a insert for each new row of data
That’s all there is to it. The League package makes it much, much easier to handle CSV files and has a lot more advanced features than I am using here. Also note that you can use any combination of these two. If you wish you can use MySQLi with League’s CSV package, you do not have to use PDO.
Wrapping Up
That’s about it. One final thing to note is that you can also pull the CSV in from a file upload. To do that just have your upload form pass to the script and check the file Superglobal for your file. Then you can grab $_FILE['file']['tmp_name'] and pass it into fopen() on the first example, and into new SplFileObject() and change Reader::createFromPath() to Reader::createFromFileObject() for the second example. Remember to validate, validate, validate if you a taking data from the wild though.
I hope these two different ways to import data into MySQL from a CSV file have been helpful. Let me know if you have any questions.
