Import Data from CSV File to MySQL Using PHP

Do you want to import data from CSV file to MySql Database using PHP?

Probably every PHP developer needs to deal with CSV file import (we also called it as a comma separated data). So basically developer needs get the data from the CSV file and insert into the MySQL database.

In this tutorial I am going to help to import data easily from CSV file using a very basic PHP script

Let’s start with the basic example:

Sample CSV File to Import:

Here we are importing users from users.csv file, take a look at below file, which is having basic details of users like Name, Mobile Number and Email address.

of course this is just a demo, you can use your own file to import with any format or number of columns just make sure to focus on each column accordingly

let go to the next step:

Create MySQL Sample Database and required Table for this example:

We have our CSV file ready with record set, now we need to create MySQL database table to store the records, go ahead and create table using following SQL query:

CREATE TABLE  `users` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 100 ) NOT NULL ,
`mobile` VARCHAR( 20 ) NOT NULL ,
`email` VARCHAR( 150 ) NOT NULL
) ENGINE = MYISAM ;

CREATE TABLE `users` (

`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,

`name` VARCHAR( 100 ) NOT NULL ,

`mobile` VARCHAR( 20 ) NOT NULL ,

`email` VARCHAR( 150 ) NOT NULL

) ENGINE = MYISAM ;

Users Table Structure

Create PHP MySQL Database connection File:

Create database connection file called db_connection.php to connect MySQL.

Use following code to and make sure to update connection variables with your server configuration

<?php
/*
* iTech Empires:  How to Import Data from CSV File to MySQL Using PHP Script
* Version: 1.0.0
* Page: DB Connection
*/

// Connection variables
$host = "localhost"; // MySQL host name eg. localhost
$user = "root"; // MySQL user. eg. root ( if your on local server)
$password = ""; // MySQL user password  (if password is not set for your root user then keep it empty )
$database = "database"; // MySQL Database name

// Connect to MySQL Database
$con = new mysqli($host, $user, $password, $database);

// Check connection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}
?>

<?php

* iTech Empires: How to Import Data from CSV File to MySQL Using PHP Script

* Version: 1.0.0

* Page: DB Connection

// Connection variables

$host = "localhost"; // MySQL host name eg. localhost

$user = "root"; // MySQL user. eg. root ( if your on local server)

$password = ""; // MySQL user password (if password is not set for your root user then keep it empty )

$database = "database"; // MySQL Database name

// Connect to MySQL Database

$con = new mysqli($host, $user, $password, $database);

// Check connection

if ($con->connect_error) {

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

}

PHP Script to Import CSV File Data:

Create import.php file: this is the user interface where we are going to have feature to select and submit file to the server.

import.php:

<?php
/*
* iTech Empires:  How to Import Data from CSV File to MySQL Using PHP Script
* Version: 1.0.0
* Page: Import.PHP
*/

// Database Connection
require 'db_connection.php';

$message = "";
if (isset($_POST['submit'])) {
    $allowed = array('csv');
    $filename = $_FILES['file']['name'];
    $ext = pathinfo($filename, PATHINFO_EXTENSION);
    if (!in_array($ext, $allowed)) {
        // show error message
        $message = 'Invalid file type, please use .CSV file!';
    } else {

        move_uploaded_file($_FILES["file"]["tmp_name"], "files/" . $_FILES['file']['name']);

        $file = "files/" . $_FILES['file']['name'];

        $query = <<<eof
        LOAD DATA LOCAL INFILE '$file'
         INTO TABLE users
         FIELDS TERMINATED BY ','
         LINES TERMINATED BY 'n'
         IGNORE 1 LINES
        (name,mobile,email)
eof;
        if (!$result = mysqli_query($con, $query)) {
            exit(mysqli_error($con));
        }
        $message = "CSV file successfully imported!";
    }
}

// View records from the table
$users = '<table class="table table-bordered">
<tr>
    <th>No</th>
    <th>Name</th>
    <th>Mobile</th>
    <th>Email</th>
</tr>
';
$query = "SELECT * FROM users";
if (!$result = mysqli_query($con, $query)) {
    exit(mysqli_error($con));
}
if (mysqli_num_rows($result) > 0) {
    $number = 1;
    while ($row = mysqli_fetch_assoc($result)) {
        $users .= '<tr>
            <td>' . $number . '</td>
            <td>' . $row['name'] . '</td>
            <td>' . $row['mobile'] . '</td>
            <td>' . $row['email'] . '</td>
        </tr>';
        $number++;
    }
} else {
    $users .= '<tr>
        <td colspan="4">Records not found!</td>
        </tr>';
}
$users .= '</table>';
?>
<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Import Data from CSV File to MySQL Tutorial</title>
    <!-- Bootstrap CSS File  -->
    <link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css"/>
</head>
<body>
<div class="container">
    <h2>
        Tutorial: How to Import Data from CSV File to MySQL Using PHP
    </h2>
    <br><br>

    <div class="row">
        <div class="col-md-6 col-md-offset-0">
            <form enctype="multipart/form-data" method="post" action="import.php">
                <div class="form-group">
                    <label for="file">Select .CSV file to Import</label>
                    <input name="file" type="file" class="form-control">
                </div>
                <div class="form-group">
                    <?php echo $message; ?>
                </div>
                <div class="form-group">
                    <input type="submit" name="submit" class="btn btn-primary" value="Submit"/>
                </div>
            </form>
            <div class="form-group">
                <?php echo $users; ?>
            </div>
        </div>
    </div>
</div>
</body>
</html>

100

101

102

103

104

105

106

107

<?php

* iTech Empires: How to Import Data from CSV File to MySQL Using PHP Script

* Version: 1.0.0

* Page: Import.PHP

// Database Connection

require 'db_connection.php';

$message = "";

if (isset($_POST['submit'])) {

$allowed = array('csv');

$filename = $_FILES['file']['name'];

$ext = pathinfo($filename, PATHINFO_EXTENSION);

if (!in_array($ext, $allowed)) {

// show error message

$message = 'Invalid file type, please use .CSV file!';

} else {

move_uploaded_file($_FILES["file"]["tmp_name"], "files/" . $_FILES['file']['name']);

$file = "files/" . $_FILES['file']['name'];

$query = <<<eof

LOAD DATA LOCAL INFILE '$file'

INTO TABLE users

FIELDS TERMINATED BY ','

LINES TERMINATED BY 'n'

IGNORE 1 LINES

(name,mobile,email)

eof;

if (!$result = mysqli_query($con, $query)) {

exit(mysqli_error($con));

}

$message = "CSV file successfully imported!";

}

// View records from the table

$users = '<table class="table table-bordered">

<tr>

<th>Mobile</th>

<th>Email</th>

</tr>

$query = "SELECT * FROM users";

if (!$result = mysqli_query($con, $query)) {

exit(mysqli_error($con));

}

if (mysqli_num_rows($result) > 0) {

$number = 1;

while ($row = mysqli_fetch_assoc($result)) {

$users .= '<tr>

<td>' . $number . '</td>

<td>' . $row['mobile'] . '</td>

<td>' . $row['email'] . '</td>

</tr>';

$number++;

}

} else {

$users .= '<tr>

<td colspan="4">Records not found!</td>

</tr>';

}

$users .= '</table>';

<!doctype html>

<head>

<title>Import Data from CSV File to MySQL Tutorial</title>

</head>

<body>

<h2>

Tutorial: How to Import Data from CSV File to MySQL Using PHP

</h2>

<label for="file">Select .CSV file to Import</label>

</div>

<?php echo $message; ?>

</div>

</div>

</form>

<?php echo $users; ?>

</div>

</body>

</html>

We are done, try to run your code or you can easily download tutorial code from following links. let me know if you find any difficulty running the code or getting any issues.

If you want to export data from MySQL to CSV file you learn from here – Export Data from MySQL to CSV using PHP