In this tutorial I will show you how to create an application that can be used to import CSV/Excel files using PHP Mysql with Spreadsheet. We will create a simple PHP web application that contains a form for our import excel file and a table to display the data we import in the database.
See also
- How to Import and Export CSV Files Using PHP and MySQL
- How to Export excel data from Database in PHP using Spreadsheet
How to Import Excel File
You can take the data from an Excel file into Access databases in many ways. In the below code you can easily to learn how to import excel file into database table using php with mysqli.
File Structure
Take a look at the example Excel import file structure shown below. The provisioning folder contains the PHPSpreadsheet library and its dependencies. I have provided a sample import template with these files to experiment with the import process. employee.sql contains a script to create the target database. The SQL script is in the next section.
In order to import Excel files into the database:
We have to use a third-party library that can read Excel files. PHPSpreadsheet is a good recommendation, and we can get it easily using Composer – composer require phpoffice/phpspreadsheet
Install PHP Spreadsheet
- The easiest way is to use an application manager called Composer – Something like Git, quite a useful one for pulling libraries automatically. A small hassle to download and install, but a one-time effort nonetheless.
- Run composer require phpoffice/phpspreadsheet
Note: That’s all. Composer will automatically pull the latest version into the vendor/ folder.
Note:
Here are demo file in Excel and CSV you can use in your project for testing purpose. This File has used in article.
The Page interface looks like the image below.
Create Database and Table by SQL query
In first step, we need to create database and table, so here I created webscodex database and employee table. You can simply create employee table as following SQL query.
-- Database: `webscodex` -- -- Table structure for table `employee` -- CREATE TABLE `employee` ( `id` int(11) NOT NULL, `emp_id` varchar(100) NOT NULL, `full_name` varchar(100) NOT NULL, `job_title` varchar(100) NOT NULL, `department` varchar(100) NOT NULL, `gender` varchar(100) NOT NULL, `age` varchar(100) NOT NULL, `hire_date` date NOT NULL, `annual_salary` varchar(100) NOT NULL, `bonus` varchar(100) NOT NULL, `country` varchar(100) NOT NULL, `city` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Indexes for table `employee` -- ALTER TABLE `employee` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `employee` -- ALTER TABLE `employee` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=912; COMMIT;
Created Database Configuration File
In this step, we require to create database configuration file, here we will set database name, username and password. So let’s create config.php file on your root directory and put bellow code:
config.php
<?php
// Database configuration
$hostname = "localhost";
$username = "root";
$password = "";
$dbname = "webscodex";
// Create database connection
$con = new mysqli($hostname, $username, $password, $dbname);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
?>Creating UI to upload Import Template
Then create another PHP file called index.php and this PHP file will be the first page to be loaded in our browser when we access view the Project folder of our web directory. And this file will load the employee table list if the employee table is not empty, because this page allows users to import CSV/Excel files and upload data to MySQL database.
index.php
<?php session_start(); ?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Import Excel data in Database using PHP with Spreadsheet</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container text-center" style="margin-top:40px; margin-bottom: 40px;">
<h2>How to Import Excel data in Database using PHP with Spreadsheet</h2><br>
<hr><br>
<?php
if (isset($_SESSION["status"])) {
echo "<div class='alert alert-success' role='alert'>" . $_SESSION['status'] . "</div>";
unset($_SESSION["status"]);
}
?>
<form action="import_data.php" method="POST" enctype="multipart/form-data">
<div class="row">
<div class="fomr-group col-md-5 offset-2">
<input type="file" name="file_data" class="form-control" />
</div>
<div class="fomr-group col-md-5">
<button type="submit" name="import_btn" class="btn btn-success">Upload / Import</button>
</div>
</div>
</form>
</div>
<?php
// Include database
include "config.php";
$query = "SELECT * FROM employee";
$result = $con->query($query);
if ($result->num_rows > 0) {
?>
<div class="container">
<table class="table table-striped table-hover table-bordered">
<thead>
<tr>
<th>Emp Id</th>
<th>Name</th>
<th>Job Title</th>
<th>Department</th>
<th>Gender</th>
<th>Age</th>
<th>Hire date</th>
<th>Annual Salary</th>
<th>Bonus</th>
<th>Country</th>
<th>City</th>
</tr>
</thead>
<tbody>
<?php foreach ($result as $row) { ?>
<tr>
<td><?php echo $row['emp_id']; ?></td>
<td><?php echo $row['full_name']; ?></td>
<td><?php echo $row['job_title']; ?></td>
<td><?php echo $row['department']; ?></td>
<td><?php echo $row['gender']; ?></td>
<td><?php echo $row['age']; ?></td>
<td><?php echo $row['hire_date']; ?></td>
<td><?php echo $row['annual_salary']; ?></td>
<td><?php echo $row['bonus']; ?></td>
<td><?php echo $row['country']; ?></td>
<td><?php echo $row['city']; ?></td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
<?php } ?>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>Next, we’re going to create another PHP file naming import_data.php that we will use to process the data from CSV/Excel to MySQL Database. Here’s the following code:
import_data.php
<?php
session_start();
// include database configuration
include "config.php";
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
if (isset($_POST['import_btn'])) {
$fileData = $_FILES['file_data']['name'];
$allowed_ext = ['xls', 'csv', 'xlsx'];
$checking = explode(".", $fileData);
$file_ext = end($checking);
if (in_array($file_ext, $allowed_ext)) {
$targetPath = $_FILES['file_data']['tmp_name'];
/** Load $inputFileName to a Spreadsheet object **/
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($targetPath);
$excelData = $spreadsheet->getActiveSheet()->toArray();
$message = false;
$count = 0;
foreach ($excelData as $key => $row) {
if ($count > 0) {
$emp_id = $row['0'];
$full_name = $row['1'];
$job_title = $row['2'];
$department = $row['3'];
$gender = $row['4'];
$age = $row['5'];
$hire_date = date('Y-m-d', strtotime($row['6']));
$annual_salary = $row['7'];
$bonus = $row['8'];
$country = $row['9'];
$city = $row['10'];
// Already exist data the update record
$sql = "SELECT * FROM employee WHERE emp_id = '$emp_id'";
$result = $con->query($sql);
if ($result->num_rows > 0) {
$query_up = "UPDATE employee SET full_name ='$full_name', job_title ='$job_title', department ='$department', gender ='$gender', age ='$age', hire_date ='$hire_date', annual_salary ='$annual_salary', bonus ='$bonus', country ='$country', city ='$city' WHERE emp_id = '$emp_id'";
$result_up = $con->query($query_up);
$message = true;
}else{
// If New record than insert into database
$query ="INSERT INTO employee (emp_id, full_name, job_title, department, gender, age, hire_date, annual_salary, bonus, country, city)
VALUES('$emp_id', '$full_name', '$job_title', '$department', '$gender', '$age', '$hire_date', '$annual_salary', '$bonus', '$country', '$city')";
$result_in = $con->query($query);
$message = true;
}
}
$count++;
}
if ($message) {
$_SESSION['status'] = "File Imported Successfully";
header("Location: index.php");
} else {
$_SESSION['status'] = "File is not imported please try again";
header("Location: index.php");
}
}else{
$_SESSION['status'] = "Invalid File Extension please try again";
header("Location: index.php");
}
}
?>After this, you can try it on your own, and I have attached with this source code the example CSV/Excel file used for this tutorial, or you can also create a new CSV file to Import using below the below column orders.
Conclusion
In this article, I will show you how you could import Excel/CSV file using PHP and MySQL Database. This is a sample demo with example you can extra add more advance level complex logic as well as validations as per your any other requirements.
Frequently asked questions
-
How can read data from Excel File in PHP?
First, import the needed library and load the Reader of XLSX. Read the excel file using the load() function. Here test. xlsx is the file name.
-
How do I show an Excel spreadsheet in my browser?
In Excel, on the File tab, choose Browser View Options. On the Show tab, use the list to select Sheets. Select the worksheets that you want to display, and then choose OK. Save and close the workbook.
-
How do you import data from Excel to SQL using PHP?
1. Create a Database in MySQL.
2. Import CSV to MySQL in PHP.
3. Display the Saved Records.
4. Export MySQL to CSV With PHP.
You can always support by sharing on social media or recommending my blog to your friends and colleagues. If you have any suggestions or problems about this tutorial, please comment on the form below.😊
