Export data from MS EXCEL sheet to MySQL table – PHP Script

Hello Friends !!

Today i write post on Export records from EXCEL sheet to MySQL table.I have PHP script for that. I already use that script and it’s working.

I will give you whole source code and explain you how to use this script.First of all you have to download excelreader file. Here this is a zip file and you have to extract it into your project directory.

After this make one PHP file called “excel_upload.php“. Copy this code and paste in this PHP file. You have to set the fields as your table.

PHP script shown below :

<?php require_once(“excelreader/Excel/reader.php”);

$con = mysql_connect(‘HOSTNAME’, ‘USERNAME’, ‘PASSWORD’);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}

mysql_select_db(“YOUR DATABASE”, $con);

$edata = new Spreadsheet_Excel_Reader();

// Set output Encoding.
$edata->setOutputEncoding(‘CP1251′);

if($_FILES['file']['tmp_name'])
{

$edata->read($_FILES['file']['tmp_name']);
}

error_reporting(E_ALL ^ E_NOTICE);
$arr=array();
for ($i = 2; $i <= $edata->sheets[0]['numRows']; $i++)
{

for ($j = 1; $j <= $edata->sheets[0]['numCols']; $j++)
{
$arr[$i][$j]=$edata->sheets[0]['cells'][$i][$j];

}

$addsql = “insert into jos_users (`field1` ,`field1` ,`field1`,`field1`) “;
$addsql = $addsql.”VALUES (‘”.$arr[$i][1].”‘,’”.$arr[$i][2].”‘,’”.$arr[$i][3].”‘,’”.$arr[$i][4].”‘)”;

$ans=mysql_query($addsql);

}
mysql_close($con);

?>

<form name=”frm” method=”post” enctype=”multipart/form-data” id=”frm”>
<input type=”file” name=”file” class=”TextboxCss” size=”30″ /><input name=”btn_save” type=”submit” class=”Button1Css” id=”btn_save”  value=”Save”  >
</form>

In above code, first loop for rows, and second loop for columns. First loop starts from 2 because generally first row

contains title of column.

$arr is an array which fetch every row one bye one and store data in it. It is a two dimentional array.

If you have any problem with this code then comment on this post. If you have any new about any post then also comment me.

Thanks….