Php import excel to mysql

by Vincy. Last modified on July 1st, 2022.

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

In case of unknown security flaw which may collapse your system. In such 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 backup to ensure the restore.

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.

We are going to create our own PHP database client  only for importing Excel data into a database.

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 example, you may visit my earlier export data in CSV format.

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

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 the user to choose the import template via HTML form input. A sample Excel template is added with this downloadable resource.

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

In PHP, the PHPSpreadSheet library is used for reading the Excel file data and convert it into an array.

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

File Structure

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

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

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

Database script

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

--
-- 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 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.

Import Excel File into MySQL Database using PHP
    
    

        

            

                Choose Excel
                    File 
                Import
        
            
        
        
        
    
    
">
    
         
select($sqlSelect);
if (! empty($result)) {
{
?>
        
    

        

            

                
Name
                
Description

            
        
                  
        

        

            

            

        

        
    

Parse excel data using PHPSpreadsheet

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

If match found, the code will execute further 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 run the database insert and show the response.

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 = 0; $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 PHPSpreadsheet and include into the vendor directory.

Download

↑ Back to Top

How do I import an Excel spreadsheet into MySQL?

How read Excel file and insert into database in PHP?

How connect Excel to PHP?

Can we read Excel file in PHP?