Import Excel File into MySQL Database using PHP

Last modified on August 10th, 2018 by Vincy.

Database backup and restore is the most important thing in maintaining software. Periodical automatic backup is a must for any project. If in case, there is an unknown security flaw and that affects your system, then your backup is the holy grail to save you. Exporting database into a backup file may be used in future to restore your system. So it is always necessary to verify your backup file by doing a test restore. Generally, the backup file will be in SQL, Excel or CSV format. The database clients available in the market provides an option to import the backup file in these formats to restore data. We are going to create our own PHP database client to import data from an Excel file.

backup-restore

I have a  HTML form with a file upload option which only accepts Excel files. After uploading the file, I parsed Excel data to insert them into a database. I used SpreadSheet Reader library for reading the Excel file using PHP. Restoring excel backup into a database via programming will save our time. Let us study how to implement a quick restore by importing bulk data from excel files. If you are looking for export try my earlier article on export data in CSV format.

Choose Excel File to Import Data

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 by 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">
        <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>
    <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 = mysqli_query($conn, $sqlSelect);

if (mysqli_num_rows($result) > 0)
{
?>
        
    <table class='tutorial-table'>
        <thead>
            <tr>
                <th>Name</th>
                <th>Description</th>

            </tr>
        </thead>
<?php
    while ($row = mysqli_fetch_array($result)) {
?>                  
        <tbody>
        <tr>
            <td><?php  echo $row['name']; ?></td>
            <td><?php  echo $row['description']; ?></td>
        </tr>
<?php
    }
?>
        </tbody>
    </table>
<?php 
} 
?>

PHP Code to Import Excel Data to MySQL

Download and deploy PHP spreadsheet-reader library in your application vendor folder. Include the library path for accessing SpreadSheet-reader functions to read excel data into an array.

In this PHP code, I specified the array of allowed file type and check the uploaded file type is in this array. After validating the file type, the excel file is uploaded to a target and its data are parsed using SpreadSheet-reader library functions. It computes the number of sheets and runs a loop to parse data sheet by sheet. For each sheet iteration, I have created a nested loop for parsing data row by row. After reading the non-empty row data, I run the database insert and show the response. 

<?php
$conn = mysqli_connect("localhost","root","test","phpsamples");
require_once('vendor/php-excel-reader/excel_reader2.php');
require_once('vendor/SpreadsheetReader.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($conn,$Row[0]);
                }
                
                $description = "";
                if(isset($Row[1])) {
                    $description = mysqli_real_escape_string($conn,$Row[1]);
                }
                
                if (!empty($name) || !empty($description)) {
                    $query = "insert into tbl_info(name,description) values('".$name."','".$description."')";
                    $result = mysqli_query($conn, $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.";
  }
}
?>

PHP Excel Import Output

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

import-excel-file-into-mysql-database-using-php

Download

This PHP code tutorial was published on March 14, 2018.

↑ Back to Top

Share this Article