Database backup and restore is the most important thing in maintaining a 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 may be used in future to restore your system. So it is always necessary to verify your backup by doing a test restore. Generally, the backup will be in SQL, or CSV format. The database clients available in the market provides an option to the backup file in these formats to restore . 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 PHPSpreadSheet library for reading the Excel file. 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 PHPSpreadSheet library in your application vendor folder. Include the library path for accessing PHPSpreadSheet 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 in a target and its data are parsed using PHPSpradSheet 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__escape_string($conn,$Row[0]);
                }
                
                $description = "";
                if(isset($Row[1])) {
                    $description = mysqli__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 = "";
                        $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 1, .

↑ Back to Top



Source link
thanks you RSS link
( https://phppot.com/php/import-excel-file-into-mysql-database-using-php/)

LEAVE A REPLY

Please enter your comment!
Please enter your name here