Import Excel File into MySQL Database using PHP

by Vincy. Last modified on April 8th, 2023.

Database backup and restore is the most important part of maintaining software. Taking automatic periodical backup is a must for any project.

In case of unknown security flaws which may collapse your system. In such an unfortunate situation, it will be the holy grail to save you if you have a backup.

It is not only enough to take a backup by exporting your database. Also, we need to experiment with importing backups to ensure the restoration.

Backup files can be in any format. Many of the database clients support SQL, Excel or CSV format files to import external data.

We will create our own PHP database client only for importing Excel data into a database.

Restoring Excel backup into a database via programming will save us time. Let us study how to implement a quick restore by importing bulk data from Excel files. You may visit my earlier export data in CSV format if you want an export example.

This screenshot shows the output by displaying the list of imported rows from the database.

import excel to mysql output

 

What is inside?

  1. About this Excel import example
  2. File Structure
  3. Database script
  4. Creating UI to upload import template
  5. Parse Excel data using PHPSpreadsheet
  6. Output: Excel import and data listing

About this Excel import example

This example allows users to choose the import template via HTML form input. A sample Excel template is added with this downloadable resource.

With a valid file, the HTML form submitted will call PHP to process Excel parsing.

In PHP, the PHPSpreadsheet library is used to read the Excel file data and convert it into an array.

The code will execute the database insert row by row by iterating the array data.

File Structure

See the Excel import example’s file structure shown below. The vendor folder has the PHPSpreadsheet library and its dependencies.

I have given a sample import template with these files to experiment with the import operation.

The schema.sql has the script to create the target database. You can find the SQL script in the next section.

Import Excel File Structure

Database script

Import the following SQL script and create a database environment. Also, configure your database details in DataSource.php to make this example work.

--
-- Database: `import_excel`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_info`
--

CREATE TABLE `tbl_info` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` varchar(50) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

--
-- Indexes for table `tbl_info`
--
ALTER TABLE `tbl_info`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for table `tbl_info`
--
ALTER TABLE `tbl_info`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;;

Creating UI to upload import template

This HTML form with the file upload option is used to choose the Excel source. On submitting this form, the Excel file will be sent to the PHP to parse the data source. This file upload option will only allow the Excel files to choose using the accept attribute.

This code also contains the response HTML for displaying the message returned from PHP. This message is shown based on the type of response sent from PHP after Excel import.

<h2>Import Excel File into MySQL Database using PHP</h2>

	<div class="outer-container">
		<div class="row">
			<form class="form-horizontal" action="" method="post"
				name="frmExcelImport" id="frmExcelImport"
				enctype="multipart/form-data" onsubmit="return validateFile()">
				<div Class="input-row">
					<label>Choose your file. <a href="Template/import-template.xlsx"
						download>Download excel template</a></label>
					<div>
						<input type="file" name="file" id="file" class="file"
							accept=".xls,.xlsx">
					</div>
					<div class="import">
						<button type="submit" id="submit" name="import" class="btn-submit">Import
							Excel and Save Data</button>
					</div>
				</div>
			</form>
		</div>
	</div>
	<div id="response"
		class="<?php if(!empty($type)) { echo $type . " display-block"; } ?>"><?php if(!empty($message)) { echo $message; } ?></div>
<?php
$sqlSelect = "SELECT * FROM tbl_info";
$result = $db->select($sqlSelect);
if (! empty($result)) {
    ?>

    <table class='tutorial-table'>
		<thead>
			<tr>
				<th>Name</th>
				<th>Description</th>
			</tr>
		</thead>
<?php
    foreach ($result as $row) { // ($row = mysqli_fetch_array($result))
        ?>
        <tbody>
			<tr>
				<td><?php  echo $row['name']; ?></td>
				<td><?php  echo $row['description']; ?></td>
			</tr>
<?php
    }
    ?>
        </tbody>
	</table>
<?php
}
?>

Parse Excel data using PHPSpreadsheet

In this PHP code, I specified the array of allowed file types for the file-type validation in PHP. I validated the uploaded file type along with the array allowed type.

If a match is found, the code will execute other logic. Otherwise, it will return the error message to the user.

With successful validation, excel file parsing read and convert the data into an array. Each array index has an array of row data. After reading the non-empty row data, I ran the database insert and showed the response.

<?php
use Phppot\DataSource;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

require_once 'DataSource.php';
$db = new DataSource();
$conn = $db->getConnection();
require_once ('vendor/autoload.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 \PhpOffice\PhpSpreadsheet\Reader\Xlsx();

        $spreadSheet = $Reader->load($targetPath);
        $excelSheet = $spreadSheet->getActiveSheet();
        $spreadSheetAry = $excelSheet->toArray();
        $sheetCount = count($spreadSheetAry);

        for ($i = 1; $i <= $sheetCount; $i ++) {
            $name = "";
            if (isset($spreadSheetAry[$i][0])) {
                $name = mysqli_real_escape_string($conn, $spreadSheetAry[$i][0]);
            }
            $description = "";
            if (isset($spreadSheetAry[$i][1])) {
                $description = mysqli_real_escape_string($conn, $spreadSheetAry[$i][1]);
            }

            if (! empty($name) || ! empty($description)) {
                $query = "insert into tbl_info(name,description) values(?,?)";
                $paramType = "ss";
                $paramArray = array(
                    $name,
                    $description
                );
                $insertId = $db->insert($query, $paramType, $paramArray);
                // $query = "insert into tbl_info(name,description) values('" . $name . "','" . $description . "')";
                // $result = mysqli_query($conn, $query);

                if (! empty($insertId)) {
                    $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.";
    }
}
?>

Note: Download the PHPSpreadsheet and include it in the vendor directory.

Download

Comments to “Import Excel File into MySQL Database using PHP”

Leave a Reply to Vincy Cancel reply

Your email address will not be published. Required fields are marked *

↑ Back to Top

Share this page