One of the challenges that PHP developers face is the data formats that are often not directly compatible with the database used in the projects. In many cases, this database is MySQL. However, the data from the client’s end is often delivered in Excel or CSV ( two formats that are popular among the managers all over the world).
Check out my popular article on how to export html table data into excel/csv using jquery
Since it is a very common task, I believe every developer should know how to Import data from Excel and CSV files to MySQL databases. For those with no clue, follow this article.
Contents
Prerequisites
For the purpose of this tutorial, I assume that you have a PHP application installed on a web server. My setup is:
- PHP 7.1
- MySQL
I also have a sample CSV file, named data.csv. I will host my app on Cloudways PHP web hosting and demonstrate the steps of the tutorials.
Create the Database & Table
The first step is the creation of the MySQL database into which the data would be imported. If you are on localhost, use phpMyAdmin to create the database. Since I am using Cloudways, I could easily create a database without any hassles.
The next step is the creation of a table named users. Use the following SQL query to create the table:
CREATE TABLE IF NOT EXISTS `users` (
`userId` int(8) NOT NULL,
`userName` varchar(55) NOT NULL,
`password` varchar(55) NOT NULL,
`firstName` varchar(255) NOT NULL,
`lastName` varchar(255) NOT NULL
)
Setup Database Connection
The popular practice for setting up database connections is through mysqli_connect query:
$conn = mysqli_connect(“localhost”, “database”, “username”, “password”);
Import CSV to MySQL in PHP
After the database has been created, I next need to upload the CSV file via an HTML form. For this, I will use HTML File uploader in a simple Bootstrap form.
Create a file and name it index.php. This is a simple form for uploading CSV file. It will also show the results in a simple table on the same page. When the user submits the form, all the entries in the CSV file will be saved in the database.
Start by adding Bootstrap CDN to index.php.
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2><img src="import-csv.png" width="50px"/>Import CSV file into Mysql using PHP</h2>
<div id="response" class="<?php if(!empty($type)) { echo $type . " display-block"; } ?>"><?php if(!empty($message)) { echo $message; } ?></div>
<div class="outer-scontainer">
<div class="row">
<form class="form-horizontal" action="" method="post"
name="frmCSVImport" id="frmCSVImport" enctype="multipart/form-data">
<div class="input-row">
<label class="col-md-4 control-label">Choose CSV
File</label> <input type="file" name="file"
id="file" accept=".csv"> <br>
<button type="submit" id="submit" name="import"
class="btn btn-success">Import</button>
<br />
</div>
</form>
</div>
<?php
$sqlSelect = "SELECT * FROM users";
$result = mysqli_query($conn, $sqlSelect);
if (mysqli_num_rows($result) > 0) {
?>
<table id='userTable'>
<thead>
<tr>
<th>User ID</th>
<th>User Name</th>
<th>First Name</th>
<th>Last Name</th>
</tr>
</thead>
<?php
while ($row = mysqli_fetch_array($result)) {
?>
<tbody>
<tr>
<td><?php echo $row['userId']; ?></td>
<td><?php echo $row['userName']; ?></td>
<td><?php echo $row['firstName']; ?></td>
<td><?php echo $row['lastName']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php } ?>
</div>
</div>
</div>
</div>
</body>
</html>
Here is how the user interface of importing excel / csv into mysql would like:
Validation for Import CSV into Mysql by jQuery
I will use jQuery for form validation. I will link the jQuery file in index.php:
$(document).ready(function() {
$("#frmCSVImport").on("submit", function () {
$("#response").attr("class", "");
$("#response").html("");
var fileType = ".csv";
var regex = new RegExp("([a-zA-Z0-9\s_\\.\-:])+(" + fileType + ")$");
if (!regex.test($("#file").val().toLowerCase())) {
$("#response").addClass("error");
$("#response").addClass("display-block");
$("#response").html("Invalid File. Upload : <b>" + fileType + "</b> Files.");
return false;
}
return true;
});
});
Check out Top 20 most useful jquery codes for web application
Insert CSV File data into MYSQL Database
The following snippet will add the data in the CSV file directly into the database:
if (isset($_POST["import"])) {
$fileName = $_FILES["file"]["tmp_name"];
if ($_FILES["file"]["size"] > 0) {
$file = fopen($fileName, "r");
while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
$sqlInsert = "INSERT into users (userId,userName,password,firstName,lastName)
values ('" . $column[0] . "','" . $column[1] . "','" . $column[2] . "','" . $column[3] . "','" . $column[4] . "')";
$result = mysqli_query($conn, $sqlInsert);
if (! empty($result)) {
$type = "success";
$message = "CSV Data Imported into the Database";
} else {
$type = "error";
$message = "Problem in Importing CSV Data";
}
}
}
}
After importing the data from the CSV file, it is displayed in a grid view. The code to retrieve the stored data and the grid view is as follows:
<?php
$sqlSelect = "SELECT * FROM users";
$result = mysqli_query($conn, $sqlSelect);
if (mysqli_num_rows($result) > 0) {
?>
<table id='userTable'>
<thead>
<tr>
<th>User ID</th>
<th>User Name</th>
<th>First Name</th>
<th>Last Name</th>
</tr>
</thead>
<?php
while ($row = mysqli_fetch_array($result)) {
?>
<tbody>
<tr>
<td><?php echo $row['userId']; ?></td>
<td><?php echo $row['userName']; ?></td>
<td><?php echo $row['firstName']; ?></td>
<td><?php echo $row['lastName']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php } ?>
Conclusion
As you could see, extracting data from a CSV file and into a database ( I used MySQL in the tutorial) is a simple matter. In many cases, this often forms a small but important part of many projects. If you wish to see the application in action, here’s a live demo.
Do leave a comment if you need help in implementing the idea into your projects.
MT Extract data from CSV files into MySQL database easily
I really like this post and the other ones on your site and have bookmarked it for future reference.
I was hoping you might help me add to your code. I want to cut the file on large files and somehow refresh to keep the server from timing out. A progress bar would be great. If you could point me in the right direction, I would appreciate it.