How to upload or import an Excel File into Mysql Database using spout Library using PHP

This tutorial explains how to upload or import Excel file into MySql Database using PHP.

 



Introduction

Spout Library is used to read and write spreadsheet files (CSV, XLSX, Xls and ODS), in a fast and scalable way.

In this tutorial, we will learn how to import an Excel sheet into database using Spout Library. For that, we will create a Database to insert an excel data into table, a form to make a view to upload an excel sheet and also create a PHP script to perform operation to insert data into Database.

 

Download Spout Library

We need to download Spout Library to use to retrieve data from an Excel sheet.

 

Create a Database

To insert excel data into MySql database, we need to create a database named as ‘demo_data’ and create a table named as ‘excel_data’

--
-- Database: `demo_data`
--

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

--
-- Table structure for table `excel_data`
--

CREATE TABLE IF NOT EXISTS `excel_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` int(11) NOT NULL,
  `email` int(11) NOT NULL,
  `phone` int(11) NOT NULL,
  `city` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) 

 

Demo Excel Sheet

I am going to show demo excel sheet to be uploaded in this example.
Excelsheet

 

Create a Form

We will create a form to upload an excel sheet, So create a new file form.html.
Here is the code:

<form  action="http://localhost/uploadExcel.php" method="POST" 
           enctype="multipart/form-data">

  <input type="file"  name="file" >
 
 <input type= "submit" value ="Upload" >
 
</form>

 
form
 

Create a PHP File

Now, we will create a file named as uploadExcel.php. Within this file, we will use PHP Script to insert data into table. We need to extract a downloaded zip file and do not forget to include file of Spout library in uploadExcel.php file.
Here is the script to get data from excel sheet.

<?php 

$servername = "localhost";
$username = "your user name";
$password = "your root password";
$dbname = "demo_data";

// Create connection

$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
} 

echo "connected";

use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;

// Include Spout library 
require_once 'here is your path of spout library/spout-2.4.3/src/Spout/
                                             Autoloader/autoload.php';

// check file name is not empty
if (!empty($_FILES['file']['name'])) {
     
    // Get File extension eg. 'xlsx' to check file is excel sheet
    $pathinfo = pathinfo($_FILES["file"]["name"]);
    
    // check file has extension xlsx, xls and also check 
    // file is not empty
   if (($pathinfo['extension'] == 'xlsx' || $pathinfo['extension'] == 'xls') 
           && $_FILES['file']['size'] > 0 ) {
        
        // Temporary file name
        $inputFileName = $_FILES['file']['tmp_name']; 
   
        // Read excel file by using ReadFactory object.
        $reader = ReaderFactory::create(Type::XLSX);

        // Open file
        $reader->open($inputFileName);
        $count = 1;
        $rows = array(); 
        
        // Number of sheet in excel file
        foreach ($reader->getSheetIterator() as $sheet) {
            
            // Number of Rows in Excel sheet
            foreach ($sheet->getRowIterator() as $row) {

                // It reads data after header. In the my excel sheet, 
                // header is in the first row. 
                if ($count > 1) { 

                    // Data of excel sheet
                    $data['name'] = $row[0];
                    $data['email'] = $row[1];
                    $data['phone'] = $row[2];
                    $data['city'] = $row[3];
                    
                    // Push all data into array to be insert as 
                    // batch into MySql database.  
                    array_push($rows, $data);
                }
                $count++;
            }

            // Print All data
            print_r($rows);

            // Now, here we can insert all data into database table.

        }

        // Close excel file
        $reader->close();

    } else {

        echo "Please Select Valid Excel File";
    }

} else {

    echo "Please Select Excel File";
    
}
?>



The following two tabs change content below.
Abhay Anand has experience in the field of Software Development. He can program Android, PHP, JavaScript, Html, Css. He develops web and Android applications. You can reach him at anand.abhay1910@gmail.com
  • ammni

    while downloading library it shows 404 .. :(

    • Hi Ammni,

      Thanks to inform me.. I have updated download link. Please check

  • Aseel

    Uncaught exception ‘Box\Spout\Common\Exception\IOException
    help me please :(

    • please include spout library with correct path

  • Koushal Sethi

    not working yet

    • abhayanand1910

      Check your library path

  • Koushal Sethi

    Uncaught exception ‘BoxSpoutCommonExceptionIOException’

  • miceman18a

    When trying to download spout. I am presented with a 404 error.

    • abhayanand1910

      Thanks to inform me, Now we can download spout library

  • naveen P

    Hi
    It’s printing the data fine but not pushing it to database.

    • abhayanand1910

      I think, you miss something, check array index name and database column name

  • Abhijeet Choubey

    Hi,
    when i click the upload button, its showing this line.

    connectedPlease Select Excel File