Excel to MySQL DB

Help
Anonymous
2010-03-03
2013-05-02

  • Anonymous
    2010-03-03

    I am probably missing something quite simple, however when I attempt to use this class to update my database, only 3 of the 4 fields update, and the 4th, removes all the data. The spreadsheet I am using only has 4 columns; sku, description, feature list, and specs. It is the specs that doesn't update. Please see below for my code:

    <?php
    require_once 'reader.php';
    $Host = "...";
    $User = "...";
    $Password = "...";
    $DBname = "...";
    $Link = mysql_connect ($Host, $User, $Password) or die ('Database error.');
    mysql_select_db($DBname);
    $filename="updates.xls";
    $prod=parseExcel($filename);
    function parseExcel($excel_file_name_with_path)
    {
        $data = new Spreadsheet_Excel_Reader();
        // Set output Encoding.
        $data->setOutputEncoding('UTF-8');
        $data->read($excel_file_name_with_path);
    $count=0;
        for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
            for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
                if($j==1){
                    $sku=trim($data->sheets[0]['cells'][$i][$j]);
                        }
                if ($j==2){
                    $desc=addslashes(trim($data->sheets[0]['cells'][$i][$j]));
                        }
                if ($j==3){
                    $list=addslashes(trim($data->sheets[0]['cells'][$i][$j]));
                        }
                if ($j==4){
                    $specs=addslashes(trim($data->sheets[0]['cells'][$i][$j]));
                        }
            }
    $count++;
    $Query72 ="UPDATE inventory set description='$desc', list='$list', specs='$specs' where sku like '$sku%'";
    mysql_query ($Query72) or die ('Database errorr.'); 
    if (mysql_affected_rows() >= 1) {print("ROW $count - update successful!<br>");} else {print("ROW $count - update failed!<br>");}
        }
    
    }
    ?>
    

    and my spreadsheet looks like this with | delimiting the cells:

    || AHSD || Waterproof, breathable,… || Performance fit # Waterpr… || Avg Weight: 1… ||

    Thanks for any help!!

     

  • Anonymous
    2010-03-04

    Sorry, something went wrong with the post :S

     
  • mmp
    mmp
    2010-03-04

    As a first step in debugging, I would suggest printing out (or somehow saving) the values of $Query72. This would quickly separate issues with reading from the spreadsheet against issues on the sql side.

    One possibility is the presence of special characters in the specs field.

    In any case, seeing the values of $Query72 would narrow down the possibilities.

    Thanks,
    Mark