Read excel file and import data into MySQL database using PHPExcel

  •  
  •  
  •  
  •  
  •  
  •  

Sometimes, we need to read a huge amount of data from excel files (.xls or .xlsx in extension) and have to store these data into a database for future use. We can achieve this by using PHP, PHPExcel library, and MySQL. this So, in this tutorial, I will explain to you how to read excel file and import data into MySQL database using PHPExcel. PHPExcel is a PHP library for reading and writing spreadsheet files. In this tutorial, we will use MySQL database for storing data extracted from excel file.

Also, you can enjoy the live demo example of read excel file and import data into MySQL database using PHPExcel for better understanding. As well as you can get the full source code from the download link.

Read excel file and import data into MySQL database using PHPExcel

MySQL Database Table

CREATE TABLE IF NOT EXISTS `user_details` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `mobile` bigint(10) NOT NULL,
  `country` varchar(50) NOT NULL
);

If you want to store or import excel data into MySQL database, you need a database table. So, create the user_details table in the database. Copy the above query and execute in your database query editor.

Database Configuration (db.php)

<?php
define('HOSTNAME','localhost');
define('DB_USERNAME','username');
define('DB_PASSWORD','password');
define('DB_NAME', 'database-name');

//global $con;
$con = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME) or die ("error");
// Check connection
if(mysqli_connect_errno($con))	echo "Failed to connect MySQL: " .mysqli_connect_error();
?>

The above PHP code will establish the connection with your MySQL database. Just replace the username, password, and database-name with your credentials.

Upload Form (index.php)

<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post" enctype="multipart/form-data">
    Upload excel file : 
    <input type="file" name="uploadFile" value="" />
    <input type="submit" name="submit" value="Upload" />
</form>

A simple HTML form for uploading excel file into the server. Excel file with both .xls and .xlsx extensions are applicable in this example.

Read excel file and import into MySQL table (index.php)

<?php
if(isset($_POST['submit'])) {
     if(isset($_FILES['uploadFile']['name']) && $_FILES['uploadFile']['name'] != "") {
        $allowedExtensions = array("xls","xlsx");
        $ext = pathinfo($_FILES['uploadFile']['name'], PATHINFO_EXTENSION);
        if(in_array($ext, $allowedExtensions)) {
           $file_size = $_FILES['uploadFile']['size'] / 1024;
           if($file_size < 50) {
               $file = "uploads/".$_FILES['uploadFile']['name'];
               $isUploaded = copy($_FILES['uploadFile']['tmp_name'], $file);
               if($isUploaded) {
                    include("db.php");
                    include("Classes/PHPExcel/IOFactory.php");
                    try {
                        //Load the excel(.xls/.xlsx) file
                        $objPHPExcel = PHPExcel_IOFactory::load($file);
                    } catch (Exception $e) {
                         die('Error loading file "' . pathinfo($file, PATHINFO_BASENAME). '": ' . $e->getMessage());
                    }
                    
                    //An excel file may contains many sheets, so you have to specify which one you need to read or work with.
                    $sheet = $objPHPExcel->getSheet(0);
                    //It returns the highest number of rows
                    $total_rows = $sheet->getHighestRow();
                    //It returns the highest number of columns
                    $total_columns = $sheet->getHighestColumn();
                          
                    echo '<h4>Data from excel file</h4>';
                    echo '<table cellpadding="5" cellspacing="1" border="1" class="responsive">';
                  
                    $query = "insert into `user_details` (`id`, `name`, `mobile`, `country`) VALUES ";
                    //Loop through each row of the worksheet
                    for($row =2; $row <= $total_rows; $row++) {
                        //Read a single row of data and store it as a array.
                        //This line of code selects range of the cells like A1:D1
                        $single_row = $sheet->rangeToArray('A' . $row . ':' . $total_columns . $row, NULL, TRUE, FALSE);
                        echo "<tr>";
                        //Creating a dynamic query based on the rows from the excel file
                        $query .= "(";
                        //Print each cell of the current row
                        foreach($single_row[0] as $key=>$value) {
                            echo "<td>".$value."</td>";
                            $query .= "'".mysqli_real_escape_string($con, $value)."',";
                        }
                        $query = substr($query, 0, -1);
                        $query .= "),";
                        echo "</tr>";
                    }
                    $query = substr($query, 0, -1);
                    echo '</table>';
                  
                    // At last we will execute the dynamically created query an save it into the database
                    //mysqli_query($con, $query);
                    if(mysqli_affected_rows($con) > 0) {    
                        echo '<span class="msg">Database table updated!</span>';
                    } else {
                        echo '<span class="msg">Can\'t update database table! try again.</span>';
                    } 
                    // Finally we will remove the file from the uploads folder (optional) 
                    unlink($file);
                } else {
                    echo '<span class="msg">File not uploaded!</span>';
                }
            } else {
                echo '<span class="msg">Maximum file size should not cross 50 KB on size!</span>';	
            }
        } else {
            echo '<span class="msg">This type of file not allowed!</span>';
        }
    } else {
        echo '<span class="msg">Select an excel file first!</span>';
    }
}
?>

Example Explained

First, we will go through the validation process. We will check whether the file is uploaded or not, if uploaded then we will check the file extension, and also we will check the file size. Here in this example, the maximum file size is limited to 50 KB in size.

pathinfo($_FILES[‘uploadFile’][‘name’], PATHINFO_EXTENSION) – Returns the extension of the file.

$objPHPExcel->getSheet(0) – An excel file may contain many sheets, so you have to specify which one you need to read or work with.

$sheet->getHighestRow() – Returns the highest number of rows.

$sheet->getHighestColumn() – Returns the highest number of columns.

$sheet->rangeToArray(‘A’ . $row . ‘:’ . $highest_column . $row, NULL, TRUE, FALSE) – Read a single row of data and store it as an array.

unlink($file) – Removes the file from the specified path.

Complete source code – Read excel file and import data into MySQL database using PHPExcel

Try the live demo example of Read excel file and import data into MySQL database using PHPExcel. Download the complete source code from the below download link and please like and share the tutorial link with others.

About Mitrajit

15 comments on “Read excel file and import data into MySQL database using PHPExcel

    1. from where you want to get the DateTime? Is it from the system or from excel file? Whatever, if you need to add another extra field (DateTime) then you should add another column to the table.

    2. $dateformat = PHPExcel_Style_NumberFormat::toFormattedString($value,PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);

  1. I was wondering how would you change your code to go through multiple worksheets in an excel file and be able to process any size of excel file and import the data to mysql? Also, how would you incorporate creating a database table depending on the excel sheet into the code?

    1. change to move_uploaded_file php function for upload any file, copy is deprecated function on php new version,
      ths regards

  2. I am getting following error on running this script in Godaddy linux server

    Error loading file “temp.xlsx”: ZipArchive library is not enabled

  3. Hello Mitrajit,
    This is wonderful post but I am suffering with a issue when I save zipcode from excel it stripping leading 0 how can I save this in my database please guide.
    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove you are a human *