Import CSV File into MySQL using PHP

It's very time consuming to import too many records one by one into MySQL database. To make it convenient, we can implement CSV (comma-separated values) file data import functionality to handle bulk data import. This can be very useful as you just need a CSV file of all records and use that CSV file to import records into MySQL database. So in this tutorial, you will learn how to import bulk data from CSV file into MySQL database by uploading CSV file. We have also created an example script to upload and import CSV file records into MySQL database and display them.


So let’s start the coding. Before begin, take a look at files structure used for this tutorial.

• index.php


• db_connect.php


• import.php

Step1: Create Database Table
In this tutorial we will import employee records into MySQL Database. So first we will create MySQL database table emp to store employee records.

CREATE TABLE IF NOT EXISTS `emp` (
`emp_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`emp_name` varchar(255) NOT NULL COMMENT 'employee name',
`emp_email` varchar(100) NOT NULL,
`emp_salary` double NOT NULL COMMENT 'employee salary',
`emp_age` int(11) NOT NULL COMMENT 'employee age',
PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;


Step2: Create Database Connection
After creating MySQL table emp, we will create db_connect.php file to make connection with MySQL database.
<?php
/* Database connection start */
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "demos";
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
?>


Steps3: Include Bootstrap and jQuery Files
As in this tutorial we have created HTML using Bootstrap, so we include Bootstrap files and also jQuery in head tag in index.php.
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>


Steps4: Create CSV File Upload Form and Display Records

Now in index.php, we will create a FORM to upload employee CSV file and also create HTML to display imported employee records.

<div class="container">
<div class="panel panel-default">
<div class="panel-body">
<br>
<div class="row">
<form action="import.php" method="post" enctype="multipart/form-data" id="import_form">
<div class="col-md-3">
<input type="file" name="file" />
</div>
<div class="col-md-5">
<input type="submit" class="btn btn-primary" name="import_data" value="IMPORT">
</div>
</form>
</div>
<br>
<div class="row">
<table class="table table-bordered">
<thead>
<tr>
<th>Emp Id</th>
<th>Emp Name</th>
<th>Emp Email</th>
<th>Emp Age</th>
<th>Salary ($)</th>
</tr>
</thead>
<tbody>
<?php
$sql = "SELECT emp_id, emp_name, emp_email, emp_salary, emp_age FROM emp ORDER BY emp_id DESC LIMIT 10";
$resultset = mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));
if(mysqli_num_rows($resultset)) {
while( $rows = mysqli_fetch_assoc($resultset) ) {
?>
<tr>
<td><?php echo $rows['emp_id']; ?></td>
<td><?php echo $rows['emp_name']; ?></td>
<td><?php echo $rows['emp_email']; ?></td>
<td><?php echo $rows['emp_salary']; ?></td>
<td><?php echo $rows['emp_age']; ?></td>
</tr>
<?php } } else { ?>
<tr><td colspan="5">No records to display.....</td></tr>
<?php } ?>
</tbody>
</table>
</div>
</div>
</div>
</div>




Steps5: Handle CSV File Import
Now we will handle functionality to import employee CSV file data into MySQL Database in import.php. We will check for valid CSV file and then read file and insert/update employee records in emp MySQL database table.
<?php
include_once("../db_connect.php");
if(isset($_POST['import_data'])){
// validate to check uploaded file is a valid csv file
$file_mimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain');
if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'],$file_mimes)){
if(is_uploaded_file($_FILES['file']['tmp_name'])){
$csv_file = fopen($_FILES['file']['tmp_name'], 'r');
//fgetcsv($csv_file);
// get data records from csv file
while(($emp_record = fgetcsv($csv_file)) !== FALSE){
// Check if employee already exists with same email
$sql_query = "SELECT emp_id, emp_name, emp_salary, emp_age FROM emp WHERE emp_email = '".$emp_record[2]."'";
$resultset = mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));
// if employee already exist then update details otherwise insert new record
if(mysqli_num_rows($resultset)) {
$sql_update = "UPDATE emp set emp_name='".$emp_record[1]."', emp_salary='".$emp_record[3]."', emp_age='".$emp_record[4]."' WHERE emp_email = '".$emp_record[2]."'";
mysqli_query($conn, $sql_update) or die("database error:". mysqli_error($conn));
} else{
$mysql_insert = "INSERT INTO emp (emp_name, emp_email, emp_salary, emp_age )VALUES('".$emp_record[1]."', '".$emp_record[2]."', '".$emp_record[3]."', '".$emp_record[4]."')";
mysqli_query($conn, $mysql_insert) or die("database error:". mysqli_error($conn));
}
}
fclose($csv_file);
$import_status = '?import_status=success';
} else {
$import_status = '?import_status=error';
}
} else {
$import_status = '?import_status=invalid_file';
}
}
header("Location: index.php".$import_status);
?>



You can view the live demo from the Demo link and can download the script from the Download link below.
Demo [sociallocker]Download[/sociallocker]