CODEWITHSUNDEEP
phpmysqlexcel
bzbz
bzbz

Reputation: 182

Read excel file and store it to mysql database

I have the following code which is giving an error on execution.

<?php
require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('exceltestsheet.xlsx');

$conn = mysql_connect("localhost","root","");
mysql_select_db("exceltest",$conn);
 for ($x = 2; $x <= count($data->sheets[0]["cells"]); $x++) {
   // $sno = $data->sheets[0]["cells"][$x][1];
    $name = $data->sheets[0]["cells"][$x][1];
    $extension = $data->sheets[0]["cells"][$x][2];
    $email = $data->sheets[0]["cells"][$x][3];
    $sql = "INSERT INTO mytable (name,extension,email) 
        VALUES ('$name',$extension,'$email')";
    echo $sql."\n";
    mysql_query($sql);
 }
?>

The error is: Deprecated: Assigning the return value of new by reference is deprecated in C:\wamp\www\xltodb\Excel\reader.php on line 262 and it also says The filename exceltestsheet.xlsx is not readable.

And the code for reader.php is: 

 function Spreadsheet_Excel_Reader()
 {
     $this->_ole =& new OLERead();
     $this->setUTFEncoder('iconv');
 }

this is the line where it is showing the error.

Upvotes: 1

Views: 10592

Answers (1)

AWA
AWA

Reputation: 303

check this out

    <?php
require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('siteb_ceccwo.xls');

$con=mysqli_connect("localhost","root","","exceltest");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

// Create table
$sql="CREATE TABLE siteb_ceccwo(

    quote_number VARCHAR(100),
    line_no VARCHAR(100),
    item_no VARCHAR(100),
    name VARCHAR(100),
    unit VARCHAR(100),
    rm VARCHAR(100),
    cwo_rm VARCHAR(100))";

// Execute query
if (mysqli_query($con,$sql))
  {
  echo "Table siteb_ceccwo created successfully";
  }
else
  {
  echo "Error creating table: " . mysqli_error($con);
  }

for($x = 2; $x <= count($data->sheets[0]["cells"]); $x++)
 {
    //$sno = $data->sheets[0]["cells"][$x][1];
    $quote_number = $data->sheets[0]["cells"][$x][1];
    $line_no = $data->sheets[0]["cells"][$x][2];
    $item_no = $data->sheets[0]["cells"][$x][3];
    $name = $data->sheets[0]["cells"][$x][4];
    $unit = $data->sheets[0]["cells"][$x][5];
    $rm = $data->sheets[0]["cells"][$x][6];
    $cwo_rm = $data->sheets[0]["cells"][$x][7];

    $res = mysqli_query($con,"INSERT INTO siteb_ceccwo 
    (quote_number, line_no, item_no,name,unit,rm,cwo_rm) VALUES ('$quote_number','$line_no','$item_no','$name','$unit','$rm','$cwo_rm')");
mysqli_close($res);
}
?>

Upvotes: 5

Related Questions