CSV files have become a widely adopted format for storing data, thanks to their simplicity and ease of use. Their table-like structure often makes them the go-to option for holding vital information such as names and numbers. When dealing with databases like MySQL, users regularly import CSV files’ contents to fill tables with extensive datasets in record time.
This tutorial is designed for anyone who wants to learn how to import CSV data into a MySQL database using PHP. The step-by-step guide starts from the basics, so no programming expertise is required. By following this article, readers can effortlessly complete the process and gain new skills in handling CSV files with ease.
Contents
- Create a Table
- Create a Database connection file
- CSV file structure
- Create an HTML Form and Handle CSV Import using PHP
- Add CSS
- Output
- Conclusion
1. Create a Table
I am using user table in the tutorial demonstration.
CREATE TABLE `user` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `username` varchar(80) NOT NULL, `fname` varchar(50) NOT NULL, `lname` varchar(50) NOT NULL, `email` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2. Create a Database connection file
Create a config.php for the database connection.
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
3. CSV file structure
I am using following CSV file structure for importing in the example –
Username,First name,Last name,Email yssyogesh,Yogesh,singh,yogesh@makitweb.com bsonarika,Sonarika,Bhadoria,bsonarika@makitweb.com rohit,Rohit,singh,rohit@makitweb.com
4. Create an HTML Form and Handle CSV Import using PHP
To import CSV data into our MySQL database using PHP, we need to create an uploads folder to store CSV file.
Next, create an HTML form that allows to select a CSV file and upload it to the uploads folder by clicking the submit button.
Once the file is uploaded, we read the data from the file and store it in an array named $importData_arr. We skip the header row of the CSV file since it doesn’t contain data. Remove the following if($skip != 0) condition to don’t skip the 1st row.
Before inserting each record from the CSV file into the MySQL database, we check for any duplicate entries to avoid adding the same data twice.
After the import is completed, the CSV file gets removed via the unlink() function. As a final step, the system showcases the last 10 records for verifying successful data importing.
<?php
include "config.php";
if(isset($_POST['but_import'])){
$target_dir = "uploads/";
$target_file = $target_dir . basename($_FILES["importfile"]["name"]);
$imageFileType = pathinfo($target_file,PATHINFO_EXTENSION);
$uploadOk = 1;
if($imageFileType != "csv" ) {
$uploadOk = 0;
}
if ($uploadOk != 0) {
if (move_uploaded_file($_FILES["importfile"]["tmp_name"], $target_dir.'importfile.csv')) {
// Checking file exists or not
$target_file = $target_dir . 'importfile.csv';
$fileexists = 0;
if (file_exists($target_file)) {
$fileexists = 1;
}
if ($fileexists == 1 ) {
// Reading file
$file = fopen($target_file,"r");
$i = 0;
$importData_arr = array();
while (($data = fgetcsv($file, 1000, ",")) !== FALSE) {
$num = count($data);
for ($c=0; $c < $num; $c++) {
$importData_arr[$i][] = mysqli_real_escape_string($con, $data[$c]);
}
$i++;
}
fclose($file);
$skip = 0;
// insert import data
foreach($importData_arr as $data){
if($skip != 0){
$username = $data[0];
$fname = $data[1];
$lname = $data[2];
$email = $data[3];
// Checking duplicate entry
$sql = "select count(*) as allcount from user where username='" . $username . "' and fname='" . $fname . "' and lname='" . $lname . "' and email='" . $email . "' ";
$retrieve_data = mysqli_query($con,$sql);
$row = mysqli_fetch_array($retrieve_data);
$count = $row['allcount'];
if($count == 0){
// Insert record
$insert_query = "insert into user(username,fname,lname,email) values('".$username."','".$fname."','".$lname."','".$email."')";
mysqli_query($con,$insert_query);
}
}
$skip ++;
}
$newtargetfile = $target_file;
if (file_exists($newtargetfile)) {
unlink($newtargetfile);
}
}
}
}
}
?>
<!-- Import form (start) -->
<div class="popup_import">
<form method="post" action="" enctype="multipart/form-data" id="import_form">
<table width="100%">
<tr>
<td colspan="2">
<input type='file' name="importfile" id="importfile">
</td>
</tr>
<tr>
<td colspan="2" ><input type="submit" id="but_import" name="but_import" value="Import"></td>
</tr>
<tr>
<td colspan="2" align="center"><span class="format">Username, First name, Last name,Email</span> </td>
</tr>
<tr>
<td colspan="2" align="center"><a href="import_example.csv" target="_blank">Download Sample</a></td>
</tr>
<tr>
<td colspan="2"><b>Instruction : </b><br/>
<ul>
<li>Enclose text field in quotes (' , " ) if text contains comma (,) is used.</li>
<li>Enclose text field in single quotes (') if text contains double quotes (")</li>
<li>Enclose text field in double quotes (") if text contains single quotes (')</li>
</ul>
</td>
</tr>
</table>
</form>
</div>
<!-- Import form (end) -->
<!-- Displaying imported users -->
<table border="1" id="userTable">
<tr>
<td>S.no</td>
<td>Username</td>
<td>First name</td>
<td>Last name</td>
<td>Email</td>
</tr>
<?php
$sql = "select * from user order by id desc limit 10";
$sno = 1;
$retrieve_data = mysqli_query($con,$sql);
while($row = mysqli_fetch_array($retrieve_data)){
$id = $row['id'];
$username = $row['username'];
$fname = $row['fname'];
$lname = $row['lname'];
$email = $row['email'];
echo "<tr>
<td>".$sno."</td>
<td>".$username."</td>
<td>".$fname."</td>
<td>".$lname."</td>
<td>".$email."</td>
</tr>";
$sno++;
}
?>
</table>
5. Add CSS
.popup_import{
border: 1px solid black;
width: 550px;
height: auto;
background: white;
border-radius: 3px;
margin: 0 auto;
padding: 5px;
}
.format{
color: red;
}
#userTable{
border-collapse: collapse;
margin: 0 auto;
margin-top: 15px;
width: 550px;
}
#but_import{
margin-left: 10px;
}
6. Output
7. Conclusion
When dealing with large amounts of data for database tables, importing CSV files into MySQL using PHP can be both efficient and convenient. In this article, the step-by-step process is explored – from creating a MySQL database and table to handling the CSV import in PHP.
It is worth noting that this tutorial serves as a foundational guide, and there are additional considerations and enhancements you can explore.
For example, you can optimize the import process for larger CSV files, implement error handling and logging for better troubleshooting, and validate and sanitize user input for enhanced security.
If you found this tutorial helpful then don't forget to share.
