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

92

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;

        // 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";
    
}
?>





  • 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

  • zacharia nyang’oro

    i dont see any link to download the library, can you help me please?

    • in the Download Spout Library section, you can see Spout Library(in blue color) link, You are able to download library

  • sergio

    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

  • Mochammad Ariff

    hey sir how to input spout to my library project?

    • Download Spout library and include it into your project

      • Mochammad Ariff

        sir, can i get source code? my email mochammadariff55@gmail.com

      • shinoy pb

        How can i do that- including the spout library?

        • unzip downloaded spout library into your project and use below code in your file

          use BoxSpoutReaderReaderFactory;
          use BoxSpoutCommonType;
          // Include Spout library
          require_once ‘here is your path of spout library/spout-2.4.3/src/Spout/
          Autoloader/autoload.php’;

  • Shujhal Singh

    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

  • Shujhal Singh

    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

    • your template path is not correct

      • Amit Chaurasia

        where is Template path?

      • Amit

        Where if template path ?

  • When You get All Data Rows then simply use MySql insert command to insert Data into database

  • Pradip Nepali

    There is no option to link database, once i upload file how it connect to db??

  • hinal arya

    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.

  • smrati

    i have uploaded excel file but i want to show icon in dashboard.please suggest ???

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

  • I think, you have done something wrong to retrieve data from excel.

  • ikhwan salihin

    may i know the insert sql for each row?

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

      • ikhwan salihin

        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

  • Samrat Singh

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

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

    • Please check you are not including Spout library correctly

      • Samrat Singh

        require_once ‘../spout/src/Spout/Autoloader/autoload.php’;
        This way I include Spout library Sir.

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

          use BoxSpoutReaderReaderFactory;
          use BoxSpoutCommonType;

          • Samrat Singh

            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”;

            }

          • $reader is array so, use print_r($reader)

          • Samrat Singh

            Thanks sir I got it.

            But this is not working for me.

            $reader->open($inputFileName);

            below this codes are not working?

          • Shubham Argade

            facing exactly same issue. plz help

          • Hey shubham, make sure your spout library path is correct.

      • Samrat Singh

        Is any changes in these lines
        use BoxSpoutReaderReaderFactory;
        use BoxSpoutCommonType;

  • Vimlesh

    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

    • If you have to insert data into Database table. Then use correct MYSQL query

  • vimlesh rawat

    cant see the data in the sql database table

    • Use MYSQL Query to insert data

      • vimlesh rawat

        AM using this but still cant get data into sql database table

        // Print All data
        print_r($rows);

        $sql=”INSERT INTO excel_data(fname,email,phone,city) VALUES(‘$row[0]’,’$row[1]’,’$row[2]’,’$row[3]’)”;
        mysql_query($sql);
        // Now, here we can insert all data into database table.

        • ikhwan salihin

          is this right?

          • I am not providing you sql to insert data into db. I am just explaining you, how to retrieve data from Excel.

    • You can use insert one by one row in database or
      Where I had print data, you can insert in a batch in Database

    • You can use MySql insert command to insert data into Mysql.

  • ikhwan salihin

    how is your sql ?

    • I assumed you know sql, But you do not know then use google to search SQL

  • RASHID ALI

    Hello sir,
    Thanks for your code
    The code is working properly..

  • teja polisetty

    hey yeah nice, it worked .
    Thank you

  • minal deogirikar

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

    • Hi Minal,
      I was doing something wrong because this running code. can you share your code

  • Please check library path

    • Adeel Ahmed

      Hello Sir
      I’m facing the same error as given above can you help me what’s wrong and how to set library path correctly??

      • Download spout library from above link. Unzip downloaded file into your project. Include it in your script page

        • Adeel Ahmed

          I did but as you told but it keep giving the this error

          Fatal error: Uncaught exception ‘BoxSpoutCommonExceptionIOException’ with message ‘Could not open C:xampptmpphp6576.tmp for reading! (Could not open C:xampptmpphp6576.tmp for reading.)’ in C:xampphtdocsGhazi_FoundationspoutsrcSpoutReaderAbstractReader.php:90 Stack trace: #0 C:xampphtdocsGhazi_FoundationImported.php(30): BoxSpoutReaderAbstractReader->open(‘C:\xampp\tmp\ph…’) #1 {main} thrown in C:xampphtdocsGhazi_FoundationspoutsrcSpoutReaderAbstractReader.php on line 90

          And this is my code
          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($reader);

          }
          $count++;
          }
          }

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

          } else {

          echo “Please Select Valid Excel File”;
          }

          } else {

          echo “Please Select Excel File”;

          }
          default:
          echo ‘Please select campus’;
          break;
          }
          }else { echo ‘Error’; }

          ?>
          Please tell me what’s wrong with this code where I’m doing wrong coding sir please do help me sir

  • Cristian Orellana

    Hi, first thanks for the information. But I’m not sure how to insert the data then into the foreach. I have the following:

    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[‘nombre_es’] = $row[0];
    $data[‘apellido_pa’] = $row[1];
    $data[‘apellido_ma’] = $row[2];

    //Here, You can insert data into database.

    mysql_query(“INSERT INTO estudiantes (nombre_es, apellido_pa , apellido_ma) VALUES($data)”);

    //print_r($data);

    }
    $count++;
    }
    }

    • Cristian Orellana

      you could teach me how to import an excel into the database using codeigniter? I’m trying to do that, but I have not had success, I’ve never done this to import files. I would be very grateful.

      PS: I am sorry if the message is not understood very well, I am Spanish speaking https://uploads.disquscdn.com/images/ed345f8c4abfc1807d31ba04a03e26003e0f875b5ed873a0f746375b8d99f4f7.png

      • Cristian Orellana

        I already solve it, now I just have to pass it to codeigniter :D. Some help please: :C

        • You can write same code in codeigniter.

          • Cristian Orellana

            but I have a question, the code of uploadExcel.php. must go in the model? should it go in the controller?

          • Cristian Orellana

            should the controller carry?

          • You can write this script in the Controller. There will be no issues.

      • Hi Cristian, you have seen error pic, you had treated array as string, so you faced some error

  • P_Som

    Hi Sir, what if the excel file have multiple sheets which sheets name are same as database table , when upload the excel file( that include multiple sheet for eg: Item, Product (these also include many rows and columns data)) , associate sheet name like Item will be inserting data into Item table from DB as well Product sheet will be inserting data into Product table. Please give some advice if want to do like this ?

    Thanks

    • Get Sheet name and use this value to insert data

      $objPHPExcel = PHPExcel_IOFactory::load(“Your File Path”);
      $objPHPExcel->getSheetNames();
      It return Array of Sheet Name

  • Welcome

  • SAN Dara

    I got error when using PHPExcel Library file. Please help. I dont know what I need to do for the Autoloader.php file. https://uploads.disquscdn.com/images/6edf19290323424e82111d87d6962da17cdaedd8b11d828c3136fb29142048a0.png

    When I upload the excel file. This issues happened.

  • bharathi

    Fatal error: Uncaught exception ‘BoxSpoutCommonExceptionIOException’ with message ‘Could not open E:xampptmpphp14E7.tmp for reading! (Could not open E:xampptmpphp14E7.tmp for reading.)’ in E:xampphtdocsuploadspout-2.4.3srcSpoutReaderAbstractReader.php:90 Stack trace: #0 E:xampphtdocsuploaduploadExcel.php(42): BoxSpoutReaderAbstractReader->open(‘E:\xampp\tmp\ph…’) #1 {main} thrown in E:xampphtdocsuploadspout-2.4.3srcSpoutReaderAbstractReader.php on line 90