...

View Full Version : PHp excel reader :databse table shows mutliple rows with empty values



ebookz
11-20-2012, 10:20 AM
I have coded a php script to import excel sheet data to mysql database..code is 80% correct ..no errors shows..also all excel sheet data were added to the table...but problem is databse table shows some empty cells with value of 0..many no of rows appeared....
*here is the phpmyadmin table image:
http://i.imgur.com/P1tn3.png

* here is my excel sheet has only 4 rows of data..
http://i.imgur.com/LjPcw.png
** here is my php code :

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>excel_upload.php</title>
</head>

<body>


<?php
if (isset($_POST['btn_save'])) {
require_once("Excel/reader.php");

include("db_connect1.php");

$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];

}

//$addsql1 = "insert into std_results (`std_id` ,`marks` ,`course_type`,`status`) ";
//$addsql = $addsql1."VALUES //(".$arr[$i][1].",".$arr[$i][2].",".$arr[$i][3].",".$arr[$i][4].")";

$a=$arr[$i][1];
$b=$arr[$i][2];
$c=$arr[$i][3];
$d=$arr[$i][4];

$addsql="INSERT INTO std_results (std_id,marks,course_type,status) VALUES ('$a','$b','$c','$d')";

$ans=mysql_query($addsql) or die("Query error: ".mysql_error());




}

}
else
{
?>

<?php
}
// anything

?>
<form name="frm" method="post" enctype="multipart/form-data" id="frm" action="<?PHP echo $_SERVER['PHP_SELF']; ?>">
<input type="file" name="file" class="TextboxCss" size="30"/>
<input name="btn_save" type="submit" class="Button1Css" id="btn_save" value="Save" />
</form>
</body>
</html>
this code is works fine..no problem about that...problem is out put of this code.... **I think u all are to can understand my problem...Plzz tell me how can I stop saving those empty rows in my db table...please help me to recover this problem.....:thumbsup::thumbsup:

BluePanther
11-20-2012, 04:30 PM
I have coded a php script to import excel sheet data to mysql database..code is 80% correct ..no errors shows..also all excel sheet data were added to the table...but problem is databse table shows some empty cells with value of 0..many no of rows appeared....
*here is the phpmyadmin table image:
http://i.imgur.com/P1tn3.png

* here is my excel sheet has only 4 rows of data..
http://i.imgur.com/LjPcw.png
** here is my php code :

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>excel_upload.php</title>
</head>

<body>


<?php
if (isset($_POST['btn_save'])) {
require_once("Excel/reader.php");

include("db_connect1.php");

$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];
}

//$addsql1 = "insert into std_results (`std_id` ,`marks` ,`course_type`,`status`) ";
//$addsql = $addsql1."VALUES //(".$arr[$i][1].",".$arr[$i][2].",".$arr[$i][3].",".$arr[$i][4].")";

$a=$arr[$i][1];
$b=$arr[$i][2];
$c=$arr[$i][3];
$d=$arr[$i][4];

$addsql="INSERT INTO std_results (std_id,marks,course_type,status) VALUES ('$a','$b','$c','$d')";

$ans=mysql_query($addsql) or die("Query error: ".mysql_error());

}

}
else
{
?>

<?php
}
// anything

?>
<form name="frm" method="post" enctype="multipart/form-data" id="frm" action="<?PHP echo $_SERVER['PHP_SELF']; ?>">
<input type="file" name="file" class="TextboxCss" size="30"/>
<input name="btn_save" type="submit" class="Button1Css" id="btn_save" value="Save" />
</form>
</body>
</html>
this code is works fine..no problem about that...problem is out put of this code.... **I think u all are to can understand my problem...Plzz tell me how can I stop saving those empty rows in my db table...please help me to recover this problem.....:thumbsup::thumbsup:

Would it really hurt to indent your code and show some good coding practise?! :) Also, NEVER check for a submit button (see my signature for details).

BluePanther
11-20-2012, 04:39 PM
Ok so, assuming Spreadsheet_Excel_Reader() works as expected, could it be picking up empty rows? Echo out $edata->sheets[0]['numRows'] and see what value it gives. If it is supplying empty rows, then perhaps there's another property in Spreadsheet_Excel_Reader() you should use? Impossible for me to say, because I don't know where this class came from.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum