How to read Excel file & Insert data into MySQL Database using PHP
Php 04-Nov-2016

How to read Excel file & Insert data into MySQL Database using PHP

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


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

CREATE TABLE  `excel` (
 `eid` VARCHAR( 100 ) NOT NULL ,
 `name` VARCHAR( 200 ) NOT NULL ,
 `email` VARCHAR( 200 ) NOT NULL ,
 `dob` VARCHAR( 40 ) NOT NULL


Edit this file as per your database credentials.

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


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


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
            for($k=1;$k<=count($data->sheets[$i][cells][$j]);$k++) // This loop is created to get data in a table format.
            $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."')";



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.