What is backup Excel file and how to import it into MySQL Database?
In this tutorial we are going to learn about backup Excel file and how to import backup Excel file data into MySQL database using PHP. Database backup and restore is the most important part in maintaining software. Every PHP developer needs to deal with Excel file import. Backup files can be in any format. You may see many of the database clients supports SQL, Excel or CSV format files to import external data.
Its actual common task for every web based software which has too much database and need to import data from Excel in MySQL database. This is a basic example.
Example image of importing Data from Excel File.
To import data from Excel file into MySQL Database table. I am doing following steps to import data from Excel file into MYSQL database using PHP Script code.
STEPS
We need to follow the following steps below to import data from Excel file.
Create directory.
Example image for directory.
It is a good practice to create the required folders on your computer so you can proceed and mange it easily.
- CSS folder for custom styling of HTML structure.
- Includes has the database connection file with name conn.php.
- Uploads folder store the excel files after uploading the Excel file using HTML form.
- The vendor folder has the PHP Spread sheet libraries.
- Create some test Excel file for uploading and save them in main folder as you can see in above image.
Example image of Excel file.
Once you have created the required folders it is time to create your HTML structure and writing the script to import the Excel file data into MySQL database.
- Launch your text editor and create a new file and save it as "index.php" in your main folder.
- Create a new file and save it as "style.css" in your CSS folder.
Create Database and a Table with name temp.
Open your phpMyAdmin and create a database with name (school) and create a table (temp) in it using the below query.
CREATE TABLE `temp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`description` varchar(50) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
We have created a simple table in database with the columns like name, description, and date.
Create Database connection.
Write the below code in your conn.php file and save it in includes folder.
<?php
$con=mysqli_connect("localhost","root","","school");
?>
Make sure to replace the placeholders with the actual values.
Now we are going to write a php code to import Excel file data.
PHP code to import Excel file data.
<?php
if (isset($_POST["import"]))
{
$allowedFileType = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
if(in_array($_FILES["file"]["type"],$allowedFileType)){
$targetPath = 'uploads/'.$_FILES['file']['name'];
move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);
$Reader = new SpreadsheetReader($targetPath);
$sheetCount = count($Reader->sheets());
for($i=0;$i<$sheetCount;$i++)
{
$Reader->ChangeSheet($i);
foreach ($Reader as $Row)
{
$name = "";
if(isset($Row[0])) {
$name = mysqli_real_escape_string($con,$Row[0]);
}
$description = "";
if(isset($Row[1])) {
$description = mysqli_real_escape_string($con,$Row[1]);
}
if (!empty($name) || !empty($description)) {
$query = "insert into temp(name,description) values('".$name."','".$description."')";
$result = mysqli_query($con, $query);
if (! empty($result)) {
$type = "success";
$message = "Excel Data Imported into the Database";
} else {
$type = "error";
$message = "Problem in Importing Excel Data";
}
}
}
}
}
else
{
$type = "error";
$message = "Invalid File Type. Upload Excel File.";
}
}
?>
Now we are going to create html form to upload the Excel file data into MySQL database.
<div class="outer-container">
<form action="" method="post"
name="frmExcelImport" id="frmExcelImport" enctype="multipart/form-data">
<div>
<label>Choose Excel
File</label> <input type="file" name="file"
id="file" accept=".xls,.xlsx">
<button type="submit" id="submit" name="import"
class="btn-submit">Import</button>
</div>
</form>
</div>
HTML form image to upload Excel file.
Now we are going to create html table to display the data of Excel file after importing the CSV file into database.
<div class="bootstrap-data-table-panel">
<div class="table-responsive">
<table class="table datatable table-striped table-bordered">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Description</th>
<th>Date</th>
</tr>
</thead>
<tbody>
<?php
$query= mysqli_query($con,"SELECT * from temp");
foreach($query as $val){
?>
<tr>
<td><?php echo $val['id']; ?></td>
<td><?php echo $val['name']; ?></td>
<td><?php echo $val['description']; ?></td>
<td><?php echo $val['date']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
</div>
HTML table image to display the Excel file data.
Now here is complete script to import the Excel file data into MySQL database using php including html form to upload the Excel file and html table to display the records that we have import.
<?php
// creat conection with database
include "includes/conn.php";
// required vendor libraries
require_once('vendor/php-excel-reader/excel_reader2.php');
require_once('vendor/SpreadsheetReader.php');
if (isset($_POST["import"]))
{
// file type validation
$allowedFileType = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
if(in_array($_FILES["file"]["type"],$allowedFileType)){
// Path to upload the excel file
$targetPath = 'uploads/'.$_FILES['file']['name'];
move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);
$Reader = new SpreadsheetReader($targetPath);
$sheetCount = count($Reader->sheets());
for($i=0;$i<$sheetCount;$i++)
{
$Reader->ChangeSheet($i);
foreach ($Reader as $Row)
{
// Parse data from excel file line by line
$name = "";
if(isset($Row[0])) {
$name = mysqli_real_escape_string($con,$Row[0]);
}
$description = "";
if(isset($Row[1])) {
$description = mysqli_real_escape_string($con,$Row[1]);
}
if (!empty($name) || !empty($description)) {
// Insert excel file data in the database
$query = "insert into temp(name,description) values('".$name."','".$description."')";
$result = mysqli_query($con, $query);
// Get status message
if (! empty($result)) {
$type = "success";
$message = "Excel Data Imported into the Database";
} else {
$type = "error";
$message = "Problem in Importing Excel Data";
}
}
}
}
}
else
{
$type = "error";
$message = "Invalid File Type. Upload Excel File.";
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>How to import Excel file data into MySQL database using PHP</title>
<!-- Bootstrap library -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
<!-- Stylesheet file -->
<link rel="stylesheet" href="css/style.css">
<div class="content-wrap">
<div class="main">
<div class="container-fluid">
<!-- /# row -->
<div id="main-content" >
<div class="row">
<div class="col-lg-12">
<div class="card alert">
<div class="card-header">
<h4>Import Excel File</h4>
<div class="card-header-right-icon">
</div>
</div>
<div class="outer-container">
<!-- excel file upload form -->
<form action="" method="post"
name="frmExcelImport" id="frmExcelImport" enctype="multipart/form-data">
<div>
<label>Choose Excel
File</label> <input type="file" name="file"
id="file" accept=".xls,.xlsx">
<button type="submit" id="submit" name="import"
class="btn-submit">Import</button>
</div>
</form>
</div>
<!-- Display status message -->
<div id="response" class="<?php if(!empty($type)) { echo $type . " display-block"; } ?>"><?php if(!empty($message)) { echo $message; } ?></div>
<div class="bootstrap-data-table-panel">
<div class="table-responsive">
<!-- Data list table -->
<table class="table datatable table-striped table-bordered">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Description</th>
<th>Date</th>
</tr>
</thead>
<tbody>
<?php
// fetch data from the database
$query= mysqli_query($con,"SELECT * from temp");
foreach($query as $val){
?>
<tr>
<td><?php echo $val['id']; ?></td>
<td><?php echo $val['name']; ?></td>
<td><?php echo $val['description']; ?></td>
<td><?php echo $val['date']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
The Bootstrap library is used to styling the Table, Form, and Buttons.
After adding the above code here is the final output after importing the Excel file data in MySQL database.
Final Output Image.
We hope you may learn how to import Excel file data into MySQL Database using PHP. Please share your thoughts and queries in the contact section.
Below is the Download link of above tutorial.
Click here to download full source code