Import CSV File into MySQL Database using PHP, In this tutorial we are going to learn how to add data to the database from excel format to mysql database. CSV stands for comma-separated values file stores the tabular data in plain text format.
Basically, CSV file format is used to import to or export from the table data. When there is needed to add the huge data into the MySQL database, it’s very time-consuming to add data one by one. In that situation, import feature helps to insert a bunch of data in one click. Using CSV file you can store all the data and import the CSV file data into the database at once using PHP and MySQL.
You can also check
Extract Data in Excel From MySQL database using PHP in Specific Date Range
Here we’ll build an example script to import student roll and Name data into the database. According to this script functionality, the user would be able to upload a CSV file of student details would be inserted into the MySQL database using PHP.
[adsense_hint]
Table of Contents
Create Database Table
Create a table name student in the database to store the students data.
CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `roll` varchar(100) NOT NULL, PRIMARY KEY (`id`) )
Connection File
db.php
This file helps to connect and select the database.
<?php
$conn=mysqli_connect("localhost","root","","studentrecord") or die("Unable to connect Database");
?>
studentrecord is the name of database
Code for import Data into Database
<?php
if(isset($_POST["submit"])){
$filename=$_FILES["file"]["name"];
if($_FILES["file"]["size"] > 0)
{
$file = fopen($filename, "r");
while (($getData = fgetcsv($file, 10000, ",")) !== FALSE)
{
$sql = "INSERT into student(name,roll)
values('".$getData[0]."','".$getData[1]."')";
$result = mysqli_query($conn,$sql);
if(!isset($result))
{
echo "<script type=\"text/javascript\">
alert(\"Invalid File:Please Upload CSV File.\");
window.location = \"index.php\"
</script>";
}
else {
echo "<script type=\"text/javascript\">
alert(\"CSV File has been successfully Imported.\");
window.location = \"index.php\"
</script>";
}
}
fclose($file);
}
}
?>
File Upload
index.php
In this file, a form will appear to choose and upload .csv file. this index.php will load all the list of student if the student table is not empty as well as this page will allow the user to import the CSV file and upload the data to MySQL Database
<?php
if(isset($_POST["submit"])){
$filename=$_FILES["file"]["name"];
if($_FILES["file"]["size"] > 0)
{
$file = fopen($filename, "r");
while (($getData = fgetcsv($file, 10000, ",")) !== FALSE)
{
$sql = "INSERT into student(name,roll)
values('".$getData[0]."','".$getData[1]."')";
$result = mysqli_query($conn,$sql);
if(!isset($result))
{
echo "<script type=\"text/javascript\">
alert(\"Invalid File:Please Upload CSV File.\");
window.location = \"index.php\"
</script>";
}
else {
echo "<script type=\"text/javascript\">
alert(\"CSV File has been successfully Imported.\");
window.location = \"index.php\"
</script>";
}
}
fclose($file);
}
}
?>
<html lang="en">
<head>
<title>TechJunkGigs</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<style>
body{
background-color:#F0F4C3;
}
</style>
</head>
<body>
<div class = "container">
<form method='POST' enctype='multipart/form-data'>
<label>Upload CSV: </label><input type='file' name='file' /><br>
<input type='submit' name='submit' value='Upload Details' />
</form>
</div>
</body>
</html>
Here i’m using some bootstrap style sheet for UI
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
User Interface for file choose and uploading
save this file as .csv file and upload to the database.
CSV file Data Uploaded successfully to the MySQL database.
Import CSV Data to Database (index.php)
- Validates the uploaded file whether it is a valid .csv file.
- The fopen() function opens the CSV file in read-only mode.
- The fgetcsv() function is used to parse the Students data from the open CSV file.
- Students data are inserted into the database using PHP and MySQL.
You can Also Check
Simple file upload in php to Server
http://www.techjunkgigs.com/simple-file-upload-php-server/
I hope this tutorial helped you to learn Import CSV File into MySQL Database using PHP. To get the latest news and updates follow us on twitter & facebook, subscribe to our YouTube channel. If you have any query then please let us know by using comment.