Import CSV data file into MySQL using PHP

CSV files are known as comma separated values. It is one of the most preferred data storage methods. PHP supports various data formats like csv, json etc. and provides functions to handle such data formats. We can parse and store csv records in database using core PHP.

Steps to import data from CSV data file to MySQL.

Step 1: Create MySQL Database
Login to phpmyadmin
Create a new database ‘classes’ in MySQL. Next create a table ‘students’ to store the details of the students in the classes database.
Run the below command to create table students inside classes database.

-- Database: `classes`
-- Table structure for table `students`

CREATE TABLE IF NOT EXISTS `students` (
  `id` int(11) NOT NULL,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

-- Database: `classes`

-- Table structure for table `students`

CREATE TABLE IF NOT EXISTS `students` (

`id` int(11) NOT NULL,

`firstname` varchar(255) NOT NULL,

`lastname` varchar(255) NOT NULL,

`email` varchar(255) NOT NULL,

`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

`modified_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

Step 2: CSV file
Check the students csv file and structure. This data is going to get imported to MySQL.

Step 3: Add database details
Add proper database details into the connection file.
db_config.php

$db_host = ''; // localhost
$db_user = ''; // mysql username
$db_password = ''; // mysql password
$db_name = ''; // mysql database name

$db_host = ''; // localhost

$db_user = ''; // mysql username

$db_password = ''; // mysql password

$db_name = ''; // mysql database name

Step 4: Putting it all together
Create a folder ‘phpimporter’ and place it inside the www folder. Add students.csv, db_config.php and index.php file to phpimporter project and paste the below code in index.php file.
index.php

getcsv_PHP();
 
function getcsv_PHP(){
    $final_arr = array();
    $inputFilename = 'students.csv'; // the location of the csv file.
    include 'db_config.php';
    $conn = new mysqli($db_host,$db_user, $db_password, $db_name);
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    echo "Data importing started<br/><br/>";
    if (($handle = fopen($inputFilename, "r")) !== FALSE){
        $length = 1000;
        $delimiter = ",";
        $i = 0;
        while ( ( $data = fgetcsv( $handle, $length, $delimiter ) ) !== FALSE ){
            if( $i != 0){
                $query = $conn->prepare("INSERT INTO users (firstname, lastname, email, created_at, modified_at) VALUES (?,?,?,?,?)");
                $query->bind_param('sssss', $data['0'], $data['1'], $data['2'], date("Y-m-d H:i:s"), date("Y-m-d H:i:s"));
                $query->execute();
                echo "Inserted ". $i. " record<br/>";
            }
            $i++;
        }
    fclose($handle);
    }
    echo "Data imported successfully.";
}

getcsv_PHP();

function getcsv_PHP(){

$final_arr = array();

$inputFilename = 'students.csv'; // the location of the csv file.

include 'db_config.php';

$conn = new mysqli($db_host,$db_user, $db_password, $db_name);

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

echo "Data importing started<br/><br/>";

if (($handle = fopen($inputFilename, "r")) !== FALSE){

$length = 1000;

$delimiter = ",";

$i = 0;

while ( ( $data = fgetcsv( $handle, $length, $delimiter ) ) !== FALSE ){

if( $i != 0){

$query = $conn->prepare("INSERT INTO users (firstname, lastname, email, created_at, modified_at) VALUES (?,?,?,?,?)");

$query->bind_param('sssss', $data['0'], $data['1'], $data['2'], date("Y-m-d H:i:s"), date("Y-m-d H:i:s"));

$query->execute();

echo "Inserted ". $i. " record<br/>";

}

$i++;

}

fclose($handle);

}

echo "Data imported successfully.";

}

Here $data[0], $data[1] are the columns in the csv. The mapping should be proper into to insert the data properly.
If you don’t want to hardcode the data as $data[0], $data[1]. check the alternate solution below

$query = $conn->prepare("INSERT INTO users (firstname, lastname, email, created_at, modified_at) VALUES('" . implode("','",$data) . "')");

1	$query = $conn->prepare("INSERT INTO users (firstname, lastname, email, created_at, modified_at) VALUES('" . implode("','",$data) . "')");

Step 5:Output
Run the project as http://localhost/phpimporter on the browser.
Check the database it should show the data present in csv is imported properly in MySQL students table.

DOWNLOAD

May 16, 2016 CodeBuddy CSV, Excel, Importer, MySQL, PHP