Fill MySQL Table With Data From CSV

/ PHP / by Paul Robinson / 0 comments
This post was published back on November 4, 2015 and may be outdated. Please use caution when following older tutorials or using older code. After reading be sure to check for newer procedures or updates to code.

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:

My MySQL table’s schema looks like this:

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.

Okay. So this whole thing is pretty simple. Let’s run down what we do:

  1. Connect to the database with MySQLi
  2. Create our prepared statement
  3. Setup our CSV path and check the file exists
  4. Create a fopen() stream of the file and assign it to the variable $h
  5. To be able to skip the first row (which usually contains the CSV headers) we set a counter variable to -1
  6. We use fgetcsv() to run through each line of data in the CSV file. More on this function in a moment
  7. 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
  8. If current() gives valid data then we can run our query.
  9. 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.
  10. 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:

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.

Again let’s go through things quickly.

  1. We use the namespace required for the CSV reader.
  2. We connect to the database using PDO
  3. We prepare our MySQL statement
  4. We create our CSV reader from our CSV file
  5. We offset by 1 row since I have a header line in my CSV, remove if you don’t
  6. Check to see if the current row has data, return false to exit the each if it doesnt. Prevents ‘Notice’ errors due to accessing non-existing array items when running out of rows if your CSV has an empty line.
  7. Use the each method 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

I'll keep your WordPress site up-to-date and working to its best.

Find out more