close
Excel is a spreadsheet used to store data in tabular form. In the web application, the Excel file format is used mainly for 2 purposes, import and export data. The import feature helps to parse data from Excel files and insert data in the database. On the other hand, the Export feature used to download data from the database and save it as a file.
Generally, you need to insert data one by one manually to add records to the database. But, when there are a large number of records that need to be inserted in the database, it’s very time-consuming to insert data manually. The import feature is a useful option to insert bulk data into the database at once dynamically. In this tutorial, we will show you how to import data from Excel file and insert into the database using PHP.
There are 2 types of Excel file formats are available to import data in PHP, CSV (.csv) and Spreadsheet (.xlsx). In our previous tutorial, we have already discussed how to import CSV file data into database with PHP. Here we will discuss the 2nd file format to import Excel file data into MySQL database using PHP.
In this example script, we will import members’ data from an Excel file in the database using PHP and MySQL.
To store the member’s data, a table is required in the database. The following SQL creates a members table with some basic fields in the MySQL database.
CREATE TABLE `members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`phone` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`status` enum('Active','Inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Based on the database table structure, the Excel file should have these fields – First Name, Last Name, Email, Phone, and Status. To import the data from Excel file, the data format should be similar to the following screen.
We will use the PhpSpreadsheet library to read Excel files and parse data. The PhpSpreadsheet is a PHP library that helps to parse data from spreadsheet file formats (.xls, .xlsx, etc).
Use composer to install PhpSpreadsheet in the script folder.
composer require phpoffice/phpspreadsheet
Alternatively, you can use our source code to install PhpSpreadsheet without composer.
Note that: All the required files including the PhpSpreadsheet library are included in our source code, you do not require to install it separately.
Before getting started, look at the file structure of the Excel data import in PHP script.
import_excel_data_with_php/ ├── dbConnect.php ├── index.php ├── importData.php ├── vendor/ ├── assets/ └── css/ ├── bootstrap.min.css └── style.css
The dbConfig.php is used to connect and select the database. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database credentials.
<?php
// Database configuration
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "root";
$dbName = "codexworld_db";
// Create database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
// Check connection
if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}
?>
Initially, the existing member’s data is listed with the Excel file import option.
If the form is already submitted,
<?php
// Load the database configuration file
include_once 'dbConfig.php';
// Get status message
if(!empty($_GET['status'])){
switch($_GET['status']){
case 'succ':
$statusType = 'alert-success';
$statusMsg = 'Member data has been imported successfully.';
break;
case 'err':
$statusType = 'alert-danger';
$statusMsg = 'Something went wrong, please try again.';
break;
case 'invalid_file':
$statusType = 'alert-danger';
$statusMsg = 'Please upload a valid Excel file.';
break;
default:
$statusType = '';
$statusMsg = '';
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Import Excel File Data with PHP</title>
<!-- Bootstrap library -->
<link rel="stylesheet" href="assets/css/bootstrap.min.css">
<!-- Stylesheet file -->
<link rel="stylesheet" href="assets/css/style.css">
<!-- Show/hide Excel file upload form -->
<script>
function formToggle(ID){
var element = document.getElementById(ID);
if(element.style.display === "none"){
element.style.display = "block";
}else{
element.style.display = "none";
}
}
</script>
</head>
<body>
<!-- Display status message -->
<?php if(!empty($statusMsg)){ ?>
<div class="col-xs-12 p-3">
<div class="alert <?php echo $statusType; ?>"><?php echo $statusMsg; ?></div>
</div>
<?php } ?>
<div class="row p-3">
<!-- Import link -->
<div class="col-md-12 head">
<div class="float-end">
<a href="javascript:void(0);" class="btn btn-success" onclick="formToggle('importFrm');"><i class="plus"></i> Import Excel</a>
</div>
</div>
<!-- Excel file upload form -->
<div class="col-md-12" id="importFrm" style="display: none;">
<form class="row g-3" action="importData.php" method="post" enctype="multipart/form-data">
<div class="col-auto">
<label for="fileInput" class="visually-hidden">File</label>
<input type="file" class="form-control" name="file" id="fileInput" />
</div>
<div class="col-auto">
<input type="submit" class="btn btn-primary mb-3" name="importSubmit" value="Import">
</div>
</form>
</div>
<!-- Data list table -->
<table class="table table-striped table-bordered">
<thead class="table-dark">
<tr>
<th>#</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Phone</th>
<th>Status</th>
<th>Created</th>
</tr>
</thead>
<tbody>
<?php
// Get member rows
$result = $db->query("SELECT * FROM members ORDER BY id DESC");
if($result->num_rows > 0){ $i=0;
while($row = $result->fetch_assoc()){ $i++;
?>
<tr>
<td><?php echo $i; ?></td>
<td><?php echo $row['first_name']; ?></td>
<td><?php echo $row['last_name']; ?></td>
<td><?php echo $row['email']; ?></td>
<td><?php echo $row['phone']; ?></td>
<td><?php echo $row['status']; ?></td>
<td><?php echo $row['created']; ?></td>
</tr>
<?php } }else{ ?>
<tr><td colspan="7">No member(s) found...</td></tr>
<?php } ?>
</tbody>
</table>
</div>
</body>
</html>
The importData.php file handles the file upload and Excel data import operations using PHP and MySQL.
PhpSpreadsheet\Reader.Xlsx() class of the PhpSpreadsheet library.load() method of Xlsx class.getActiveSheet() method.toArray() method.<?php
// Load the database configuration file
include_once 'dbConfig.php';
// Include PhpSpreadsheet library autoloader
require_once 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
if(isset($_POST['importSubmit'])){
// Allowed mime types
$excelMimes = array('text/xls', 'text/xlsx', 'application/excel', 'application/vnd.msexcel', 'application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
// Validate whether selected file is a Excel file
if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $excelMimes)){
// If the file is uploaded
if(is_uploaded_file($_FILES['file']['tmp_name'])){
$reader = new Xlsx();
$spreadsheet = $reader->load($_FILES['file']['tmp_name']);
$worksheet = $spreadsheet->getActiveSheet();
$worksheet_arr = $worksheet->toArray();
// Remove header row
unset($worksheet_arr[0]);
foreach($worksheet_arr as $row){
$first_name = $row[0];
$last_name = $row[1];
$email = $row[2];
$phone = $row[3];
$status = $row[4];
// Check whether member already exists in the database with the same email
$prevQuery = "SELECT id FROM members WHERE email = '".$email."'";
$prevResult = $db->query($prevQuery);
if($prevResult->num_rows > 0){
// Update member data in the database
$db->query("UPDATE members SET first_name = '".$first_name."', last_name = '".$last_name."', email = '".$email."', phone = '".$phone."', status = '".$status."', modified = NOW() WHERE email = '".$email."'");
}else{
// Insert member data in the database
$db->query("INSERT INTO members (first_name, last_name, email, phone, status, created, modified) VALUES ('".$first_name."', '".$last_name."', '".$email."', '".$phone."', '".$status."', NOW(), NOW())");
}
}
$qstring = '?status=succ';
}else{
$qstring = '?status=err';
}
}else{
$qstring = '?status=invalid_file';
}
}
// Redirect to the listing page
header("Location: index.php".$qstring);
?>
Import and Export CSV File using PHP and MySQL
Here, we develop a simple script to import Excel data with PHP and MySQL. The example code will help you to implement the Excel file data import functionality in the web application. You can easily enhance our Excel Import to MySQL script functionality, to add more fields or restrictions on data import based on your requirement.
Do you want to get implementation help, or enhance the functionality of this script? Click here to Submit Service Request
import.php file … gives problem while reading a xls file. However to automatically resolve the file type ,\PhpOffice\PhpSpreadsheet\IOFactory::load(‘filename’) can be used . It can read both xls as well xlsx files.