how to insert data from excel file to my sql database

Normally we insert the data in MySQL via a form or manually from phpmyadmin.

But if we have large amount of data in excel. and we want to insert these data in our sql database.

So we learn today

"how to insert data from excel to mysql"

So in this tutorial first we create a database with some coloums.

like as

   Database Name- excel_data

CREATE TABLE IF NOT EXISTS `excel_data` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(60) NOT NULL,
  `math` int(11) NOT NULL,
  `science` int(11) NOT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

In the above table we have four fields
1. student_id
2.student_name
3.math
4.science

Now we insert data via help of php from excel file.
our first focus is that our web server read the content of excel file, So we use the reader.php
this php file (reader.php) allow to read the element of excel file.
First we copy the reader.php file in "Excel" folder.
dataupload.php

<?php
require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('student_data.xls'); //path of our excel file
 
$conn = mysql_connect("localhost","YOUR USERNAME","YOUR PASSWORD");
mysql_select_db("exceldemo",$conn);//here your database name
 
for ($x = 2; $x <= count($data->sheets[0]["cells"]); $x++) {
$student_name = $data->sheets[0]["cells"][$x][1];
$math = $data->sheets[0]["cells"][$x][2];
$science = $data->sheets[0]["cells"][$x][3];

$sql = "INSERT INTO pc_detail (student_name,math,science)
VALUES ('$student_name','$math','$science')";
echo $sql."\n";
mysql_query($sql);
}
 
?>

Through the above code all excel data goes to our database.

Source Code of Above files
Click Here to Download Source Files