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.




Don't Miss

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;

        // 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];
                    
                    //Here, You can insert data into database. 
                    print_r(data);
                    
                }
                $count++;
            }
        }

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

    } else {

        echo "Please Select Valid Excel File";
    }

} else {

    echo "Please Select Excel File";
    
}
?>





112 COMMENTS

  1. hi, I’m getting an error in $reader->open($inputFileName). Where do I have to put the file?

    Fatal error: Uncaught exception ‘BoxSpoutCommonExceptionIOException’ with message ‘Could not open FY16_09_MIF Report_Dec.xlsx for reading! File does not exist.’ in C:xampphtdocslibspoutsrcSpoutReaderAbstractReader.php:115 Stack trace: #0 C:xampphtdocsimport.php(47): BoxSpoutReaderAbstractReader->open(‘FY16_09_MIF Rep…’) #1 {main} thrown in C:xampphtdocslibspoutsrcSpoutReaderAbstractReader.php on line 115

  2. Fatal error: Uncaught exception ‘BoxSpoutCommonExceptionIOException’ with message ‘Could not open /Applications/XAMPP/xamppfiles/temp/phpTMdFR4 for reading! (Could not open /Applications/XAMPP/xamppfiles/temp/phpTMdFR4 for reading.)’ in /Applications/XAMPP/xamppfiles/htdocs/import/spout-2.4.3/src/Spout/Reader/AbstractReader.php:90 Stack trace: #0 /Applications/XAMPP/xamppfiles/htdocs/import/uploadExcel.php(42): BoxSpoutReaderAbstractReader->open(‘/Applications/X…’) #1 {main} thrown in /Applications/XAMPP/xamppfiles/htdocs/import/spout-2.4.3/src/Spout/Reader/AbstractReader.php on line 90

  3. how can i solve this
    Fatal error: Uncaught exception ‘BoxSpoutCommonExceptionIOException’ with message ‘Could not open /Applications/XAMPP/xamppfiles/temp/phpTMdFR4 for reading! (Could not open /Applications/XAMPP/xamppfiles/temp/phpTMdFR4 for reading.)’ in /Applications/XAMPP/xamppfiles/htdocs/import/spout-2.4.3/src/Spout/Reader/AbstractReader.php:90 Stack trace: #0 /Applications/XAMPP/xamppfiles/htdocs/import/uploadExcel.php(42): BoxSpoutReaderAbstractReader->open(‘/Applications/X…’) #1 {main} thrown in /Applications/XAMPP/xamppfiles/htdocs/import/spout-2.4.3/src/Spout/Reader/AbstractReader.php on line 90

  4. can i upload xls file also?if yes pls send the code….in ur given code we are able to use only xlsx,csv,ods file only.

    • I do not understand you problem. Is any issues occurred while creating upload excel? If you want to display excel icon then use image if file exist.

    • Use insert command
      INSERT INTO TABLE_NAME (column1, column2, column3,…columnN) VALUES (value1, value2, value3,…valueN);

      • yup that is the right syntax, but i am lost on how to insert into table with your code . for example i have tried
        INSERT INTO TABLE_NAME (column1, column2, column3,column4) VALUES ($row[0],$row[1], $row[2], $row[3],); and for sure this is wrong. i already tried it, furthermore where should i put the line of code? is it where you put comments which is // Now, here we can insert all data into database table. ??? Sorry i cant quite understand your code

  5. $reader = ReaderFactory::create(Type::XLSX);

    This line is not working for me.
    What should I do Please let me know.
    Thanks in advance.

        • Provide full path in the require_once ‘Your Full & correct Path’ and also include

          use BoxSpoutReaderReaderFactory;
          use BoxSpoutCommonType;

          • I tried this but this not give result in foreach.
            I am facing problem in echo $render;
            Nothing to show Error occurs 500 server error.?

            require_once ‘/var/www/html/IFMR/spout/src/Spout/Autoloader/autoload.php’;
            use BoxSpoutReaderReaderFactory;
            use BoxSpoutCommonType;
            // Include Spout library
            // echo $_FILES[‘file’][‘name’];

            // 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 ) {
            // echo ‘hid’;
            // Temporary file name
            $inputFileName = $_FILES[‘file’][‘tmp_name’];

            // Read excel file by using ReadFactory object.
            $reader = ReaderFactory::create(Type::XLSX);
            echo $inputFileName;
            echo $reader;
            // Open file
            $reader->open($inputFileName);
            $count = 1;
            $rows = array();

            // Number of sheet in excel file
            foreach ($reader->getSheetIterator() as $sheet) {
            echo ‘hi’;
            // 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) {
            print_r($row);
            die;
            // 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”;

            }

          • Thanks sir I got it.

            But this is not working for me.

            $reader->open($inputFileName);

            below this codes are not working?

  6. connectedArray ( [0] => Array ( [name] => a [email] => asdsad121@gmail.com [phone] => 49846545132 [city] => asfa ) [1] => Array ( [name] => b [email] => askdhkj@gmail.com [phone] => 124842312318 [city] => asfasf ) [2] => Array ( [name] => c [email] => asf@gmail.com [phone] => 18465116 [city] => asfsafas ) ) Array ( [0] => Array ( [name] => a [email] => asdsad121@gmail.com [phone] => 49846545132 [city] => asfa ) [1] => Array ( [name] => b [email] => askdhkj@gmail.com [phone] => 124842312318 [city] => asfasf ) [2] => Array ( [name] => c [email] => asf@gmail.com [phone] => 18465116 [city] => asfsafas ) ) Array ( [0] => Array ( [name] => a [email] => asdsad121@gmail.com [phone] => 49846545132 [city] => asfa ) [1] => Array ( [name] => b [email] => askdhkj@gmail.com [phone] => 124842312318 [city] => asfasf ) [2] => Array ( [name] => c [email] => asf@gmail.com [phone] => 18465116 [city] => asfsafas ) )

    the table is empty in sql databse table

  7. Hello sir…..
    i had used this code but not working on server side….Data is Not inserted on table…please suggest something…