Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Nov 2012
    Posts
    33
    Thanks
    23
    Thanked 0 Times in 0 Posts

    Question PHp excel reader :databse table shows mutliple rows with empty values

    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:


    * here is my excel sheet has only 4 rows of data..

    ** here is my php code :
    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.....

  • #2
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    Quote Originally Posted by ebookz View Post
    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:


    * here is my excel sheet has only 4 rows of data..

    ** here is my php code :
    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.....
    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).
    Useful function to retrieve difference in times
    The best PHP resource
    A good PHP FAQ
    PLEASE remember to wrap your code in [PHP] tags.
    PHP Code:
    // Replace this
    if(isset($_POST['submitButton']))
    // With this
    if(!empty($_POST))
    // Then check for values/forms. Some IE versions don't send the submit button 
    Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.

  • Users who have thanked BluePanther for this post:

    ebookz (11-20-2012)

  • #3
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    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.
    Useful function to retrieve difference in times
    The best PHP resource
    A good PHP FAQ
    PLEASE remember to wrap your code in [PHP] tags.
    PHP Code:
    // Replace this
    if(isset($_POST['submitButton']))
    // With this
    if(!empty($_POST))
    // Then check for values/forms. Some IE versions don't send the submit button 
    Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.

  • Users who have thanked BluePanther for this post:

    ebookz (11-20-2012)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •