I have received many requests from my readers to write tutorial on Excel file import in MySQL database, so today I am going to give you this tutorial on how to read excel file and insert data into MySQL DB using PHP. I have used a php library php-excel-reader its a very simple and easy to understand library to get excel data in your MySQL database. You can also print data in same excel format in HTML and display on browser.

You can get that library from here.

Database Details:

database name => phpgang
table name => excel

db.sql

Database file run in your MySQL to create database and add data in table.

CREATE TABLE  `excel` (
 `id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 `eid` VARCHAR( 100 ) NOT NULL ,
 `name` VARCHAR( 200 ) NOT NULL ,
 `email` VARCHAR( 200 ) NOT NULL ,
 `dob` VARCHAR( 40 ) NOT NULL
) ENGINE = MYISAM ;

db.php

Edit this file as per your database credentials.

<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'username');
define('DB_PASSWORD', 'password');
define('DB_DATABASE', 'database');
$connection = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
?>

index.php

Contains HTML and PHP include library and insert records in database.

<?php

ini_set("display_errors",1);
require_once 'excel_reader2.php';
require_once 'db.php';

$data = new Spreadsheet_Excel_Reader("example.xls");

echo "Total Sheets in this xls file: ".count($data->sheets)."<br /><br />";

$html="<table border='1'>";
for($i=0;$i<count($data->sheets);$i++) // Loop to get all sheets in a file.
{    
    if(count($data->sheets[$i][cells])>0) // checking sheet not empty
    {
        echo "Sheet $i:<br /><br />Total rows in sheet $i  ".count($data->sheets[$i][cells])."<br />";
        for($j=1;$j<=count($data->sheets[$i][cells]);$j++) // loop used to get each row of the sheet
        { 
            $html.="<tr>";
            for($k=1;$k<=count($data->sheets[$i][cells][$j]);$k++) // This loop is created to get data in a table format.
            {
                $html.="<td>";
                $html.=$data->sheets[$i][cells][$j][$k];
                $html.="</td>";
            }
            $eid = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][1]);
            $name = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][2]);
            $email = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][3]);
            $dob = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][4]);
            $query = "insert into excel(eid,name,email,dob) values('".$eid."','".$name."','".$email."','".$dob."')";

            mysqli_query($connection,$query);
            $html.="</tr>";
        }
    }

}

$html.="</table>";
echo $html;
echo "<br />Data Inserted in dababase";
?>

First of all it count your sheets in excel file then get rows of each sheet and print rows of each column in html table and insert it in database.

You can get excel_reader2.php by downloading source code.

That’s all for today’s tutorial i hope it helps. Please feel free to give us your feedback in comments.