Tutorial: Import Excel or CSV file to MySQL Database using PHP with Source Code
About How to Import Excel or CSV file to MySQL Database using PHP
In this tutorial im going to show you how to Import Excel or CSV to MySQL Database file using PHP. We will create a simple PHP web application that has a form for our import feature and table to display our imported data to the database.
The tutorial source code was originally written in PHP MySQL and I have updated it into PHP MySQLi.
To start with this project, please make sure that you have already installed a local web server that can run PHP script and MySQL Database such as “XAMPP” and “WAMP”. Then also make sure that the “Apache” and “MySQL” is started (for XAMPP/WAMP, open the XAMPP/WAMP Control Panel and start the Apache and MySQL).
Now, we will create a Database in your PHPMyAdmin named “studentdb” then execute this SQL query to create a new table called “subject”.
CREATE TABLE IF NOT EXISTS `subject` ( `SUBJ_ID` INT(11) NOT NULL AUTO_INCREMENT, `SUBJ_CODE` VARCHAR(30) NOT NULL, `SUBJ_DESCRIPTION` VARCHAR(255) NOT NULL, `UNIT` INT(2) NOT NULL, `PRE_REQUISITE` VARCHAR(30) NOT NULL DEFAULT 'None', `COURSE_ID` INT(11) NOT NULL, `AY` VARCHAR(30) NOT NULL, `SEMESTER` VARCHAR(20) NOT NULL, PRIMARY KEY (`SUBJ_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=500 ;
Then inside you htdocs or document root folder create a new folder and name it as “excel”. Next, we need to create a PHP file naming “db.php” that will hold our database connection and here’s the following code:
<?php
$conn=mysqli_connect("localhost","root","") or die("Could not connect");
mysqli_select_db($conn,"studentdb") or die("could not connect database");
?>
Next, create another PHP file named “index.php”,and this PHP file will be the first page that will load to our browser when we will access/browse the “excel” folder from our web directory. And this file will load all the list of subjects if the subject table is not empty well as this page will allow the user to import the CSV/Excel file and upload the data to MySQL Database.
The page Interface will be look like the image below.
And here’s the code for the “index.php” file:
<!DOCTYPE html>
<?php
include 'db.php';
?>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Import Excel To MySQL Database Using PHP </title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="description" content="Import Excel File To MySQL Database Using php">
<link rel="stylesheet" href="css/bootstrap.min.css">
<link rel="stylesheet" href="css/bootstrap-responsive.min.css">
<link rel="stylesheet" href="css/bootstrap-custom.css">
</head>
<body>
<!-- Navbar
================================================== -->
<div class="navbar navbar-inverse navbar-fixed-top">
<div class="navbar-inner">
<div class="container">
<a class="btn btn-navbar" data-toggle="collapse" data-target=".nav-collapse">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</a>
<a class="brand" href="#">Import Excel To MySQL Database Using PHP</a>
</div>
</div>
</div>
<div id="wrap">
<div class="container">
<div class="row">
<div class="span3 hidden-phone"></div>
<div class="span6" id="form-login">
<form class="form-horizontal well" action="import.php" method="post" name="upload_excel" enctype="multipart/form-data">
<fieldset>
<legend>Import CSV/Excel file</legend>
<div class="control-group">
<div class="control-label">
<label>CSV/Excel File:</label>
</div>
<div class="controls">
<input type="file" name="file" id="file" class="input-large">
</div>
</div>
<div class="control-group">
<div class="controls">
<button type="submit" id="submit" name="Import" class="btn btn-primary button-loading" data-loading-text="Loading...">Upload</button>
</div>
</div>
</fieldset>
</form>
</div>
<div class="span3 hidden-phone"></div>
</div>
<table class="table table-bordered">
<thead>
<tr>
<th>ID</th>
<th>Subject</th>
<th>Description</th>
<th>Unit</th>
<th>Semester</th>
</tr>
</thead>
<?php
$SQLSELECT = "SELECT * FROM subject ";
$result_set = mysqli_query($conn, $SQLSELECT);
while($row = mysqli_fetch_array($result_set))
{
?>
<tr>
<td><?php echo $row['SUBJ_ID']; ?></td>
<td><?php echo $row['SUBJ_CODE']; ?></td>
<td><?php echo $row['SUBJ_DESCRIPTION']; ?></td>
<td><?php echo $row['UNIT']; ?></td>
<td><?php echo $row['SEMESTER']; ?></td>
</tr>
<?php
}
?>
</table>
</div>
</div>
</body>
</html>Next, we’re going to create another PHP file naming “import.php” that we will use to process the data from CSV/Excel to MySQL Database. Here’s the following code:
<?php
include 'db.php';
if(isset($_POST["Import"])){
echo $filename=$_FILES["file"]["tmp_name"];
if($_FILES["file"]["size"] > 0)
{
$file = fopen($filename, "r");
while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
{
//It wiil insert a row to our subject table from our csv file`
$sql = "INSERT into subject (`SUBJ_CODE`, `SUBJ_DESCRIPTION`, `UNIT`, `PRE_REQUISITE`,COURSE_ID, `AY`, `SEMESTER`)
values('$emapData[1]','$emapData[2]','$emapData[3]','$emapData[4]','$emapData[5]','$emapData[6]','$emapData[7]')";
//we are using mysql_query function. it returns a resource on true else False on error
$result = mysqli_query( $conn, $sql );
if(! $result )
{
echo "<script type=\"text/javascript\">
alert(\"Invalid File:Please Upload CSV File.\");
window.location = \"index.php\"
</script>";
}
}
fclose($file);
//throws a message if data successfully imported to mysql database from excel file
echo "<script type=\"text/javascript\">
alert(\"CSV File has been successfully Imported.\");
window.location = \"index.php\"
</script>";
//close of connection
mysqli_close($conn);
}
}
?>That’s it! That would be the end of our tutorial on Importing CSV/Excel File data to MySQL Database using PHP. By the way in this application I am using the Twitter bootstrap framework, you can download and use it like in this application.
Related tutorials: How to Import JSON File Using PDO in PHP, Import XML File in PHP, Export MySQL Database into CSV File using PHP/MySQLi, Import SQL File to MySQL Database in PHP MySQL
Download Here
