Import CSV File into MySQL Database using PHP Script

On 11/08/2017

Hi, this time I have come up with another useful php script, which will show you How to Import CSV File Data into MySQL Database using PHP. CSV Files commonly known as comma separated values is one of the most preferred data storage methods. And as a developer often you might find you in situation to import those huge csv data into database system. Though sounding simple, it's a nightmare in disguise. Here's where php comes to the rescue. With few lines of code, you can insert several hundreds of records in flash.

PHP supports several data formats like csv, json etc. and provides dedicated functions to handle such data formats. Hence without any third party solutions, we can to parse and store csv records in database using core php alone.

php script import csv into mysql database
Read Also

Create Sample MySQL Database

As an example, let's use our previous 'library' database for this tutorial. Just create a new database with name 'library' in mysql. Next you should create a table 'books' to store the details of the books in the library. For that run the below sql command.

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `library`
--
-- Table structure for table `books`
--
CREATE TABLE IF NOT EXISTS `books` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `author` varchar(30) NOT NULL,
  `isbn` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Sample CSV File

Below is the sample csv file containing the books data and we'll import this csv file to the 'books' table.

books.csv
Learning PHP MySQL & JavaScript,Robin Nixon,ISBN-13: 978-1491918661
PHP and MySQL for Dynamic Web Sites,Larry Ullman,ISBN-13: 978-0321784070
PHP Cookbook,David Sklar,ISBN-13: 978-1449363758
Programming PHP,Kevin Tatroe,ISBN-13: 978-1449392772
Modern PHP New Features and Good Practices,Josh Lockhart,ISBN-13: 978-1491905012

Now it's time to move on to the php script part.

PHP Script to Import CSV File Data into MySQL Database

The CSV import process goes like this. With PHP open the '.csv' file, parse through it one line at a time and insert into mysql db. Following is the step by step procedure for doing so.

Step-1: Set the Database Connection Variables

First let's set the database connection variables and the right csv file path to import.

<?php
//set the connection variables
$hostname = "localhost";
$username = "username";
$password = "password";
$database = "library";
$filename = "books.csv";
?>

Step-2: Establish the MySQL Database Connection

Next we open the connection to mysql database using mysqli library.

<?php
//connect to mysql database
$connection = mysqli_connect($hostname, $username, $password, $database) or die("Error " . mysqli_error($connection));
?>

Step-3: Open and Parse the CSV File

Now we should open the csv file in 'reading' mode and parse the record one at a time and insert into mysql table 'books'.

<?php
// open the csv file
$fp = fopen($filename,"r");

//parse the csv file row by row
while(($row = fgetcsv($fp,"500",",")) != FALSE)
{
    //insert csv data into mysql table
    $sql = "INSERT INTO tbl_books (name, author, isbn) VALUES('" . implode("','",$row) . "')";
    if(!mysqli_query($connection, $sql))
    {
        die('Error : ' . mysqli_error());
    }
}

fclose($fp);
?>

The line $fp = fopen($filename,"r"); will open the 'books.csv' file for reading and return the file handler to the $fp variable.

Next we loop through the entire file one record at a time. Using the line $row = fgetcsv($fp,"500",",") will fetch the complete row of the csv file one at a time, and "500" specifies the maximum length of the rows in the csv file and "," specifies the delimiter.

Finally we implode the fetched row and save it into mysql database using the INSERT query.

The line fclose($fp); closes the file handler after completely parsing through the entire file.

Step-4: Close the MySQL Database Connection

After inserting all the csv records into mysql, we finally close the database connection we established first.

<?php
//close the db connection
mysqli_close($connection);
?>

Here is the Complete PHP Script to Import CSV File into MySQL Database.

<?php
//set the connection variables
$hostname = "localhost";
$username = "username";
$password = "password";
$database = "library";
$filename = "books.csv";

//connect to mysql database
$connection = mysqli_connect($hostname, $username, $password, $database) or die("Error " . mysqli_error($connection));

// open the csv file
$fp = fopen($filename,"r");

//parse the csv file row by row
while(($row = fgetcsv($fp,"500",",")) != FALSE)
{
    //insert csv data into mysql table
    $sql = "INSERT INTO tbl_books (name, author, isbn) VALUES('" . implode("','",$row) . "')";
    if(!mysqli_query($connection, $sql))
    {
        die('Error : ' . mysqli_error());
    }
}

fclose($fp);

//close the db connection
mysqli_close($connection);
?>

Now go back to mysql and check the library database after running the php snippet. You will find the csv file records inserted into the 'books' table.

And that was all about importing csv file into mysql database using php. There's also the reverse process of exporting mysql database to csv file with php. You can check it out.

11 comments:

  1. Greetings! Thank you for taking the time to share. Question: When i use the code (below), only the first row imports into MySQL database. I've searched online for different answers, yet no one has any guidance. Could you please help??

    ReplyDelete
  2. Greetings! i've search everywhere to find an answer to my problem: My CSV file only imports the top line when i execute the code from php. It works fine when I manually import via phpmyadmin. I tried your code and it does the same thing. My code is below, can you shed some light into what I'm doing wrong? Thank you for your time!

    ReplyDelete
    Replies
    1. Hi, provide me the code and the sample csv you are trying! I'm happy to help :)

      Cheers.

      Delete
  3. Nice Article !

    Really this will help to people of PHP & MySQL Community.
    I have also prepared small demonstration on, how to import and export csv data with headers using MySQL.
    You can visit my article using below link.

    http://www.dbrnd.com/2015/09/mysql-import-and-export-csv-data-with-headers/

    ReplyDelete
  4. we are offering Website designs, services, and also make a Android & IOS Applications..... Buzz App

    ReplyDelete
  5. We have IVR System for your Bank and stock account balances and transfers, Surveys and polls, Office call routing, Call center forwarding, Simple order entry transactions, Selective information lookup (movie schedules, etc.)

    Hosted IVR

    ReplyDelete
  6. I thought this was an excellent as well as useful document to read. We've truly enjoyed doing this very cool facts.wp plugin

    ReplyDelete
  7. I learned new concept. Thanks for sharing this post.


    eCommerce website development in Chennai

    ReplyDelete
  8. The only thing, that confused me a little bit was tbl_books instead books in line 19.
    Many Thanks! It was a great help for me!

    ReplyDelete

Contact Form

Name

Email *

Message *