Hello Reader's ,

Today I am going to give you this blog on how to read excel file and insert data into MySQL DB using PHP.Here we are using php-excel-reader.php library to get excel data in MySQL database.

Let's Start.

Create Database and table.
 

Database Name => details
Table Name => excel

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

Create "db.php" file which contain database credentials.

You can Edit this file as per your database credentials.

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

Now create index.php file  which contains HTML and PHP includes 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>";
			}
			$data->sheets[$i]['cells'][$j][1];
			$eid = $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]);
			
			
			$chk="select * from excel where eid='".$eid."'";
			$rt=mysqli_query($connection,$chk);
			$finl=mysqli_num_rows($rt);
			
			$query = "insert into excel(eid,name,email,dob) values('".$eid."','".$name."','".$email."','".$dob."')";				
			mysqli_query($connection,$query);
			$html.="</tr>";	
			
		}
	}
	
}

$html.="</table>";
echo $html;
?>

You can get excel_reader2.php by downloading source code.

Please feel free to give us your feedback in comments.