Insert Query is not working with phpexcel Script

Topics: Developer Forum
Apr 28, 2012 at 4:12 AM
Edited Apr 28, 2012 at 4:20 AM

I am using phpExcel library for import data from excel(csv) file into mysql data. Every thing is working fine except mysql insert query below is my source code. Please after review my codes let me know where is problem. I have tried with an array but im fail too. Please review my mysql query and let me know which query will be better in this PhpExcel Library. Thanks in advance.

$objPHPExcel = PHPExcel_IOFactory::load("myfile.csv");
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {

$worksheetTitle     = $worksheet->getTitle();
$highestRow         = $worksheet->getHighestRow();
$highestColumn      = $worksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;

for ($row=2; $row<=$highestRow; $row++) {
$cell = $worksheet->getCellByColumnAndRow(3, $row)->getValue();

 mysql_query("INSERT INTO myTableName (`title`) VALUES ('$cell')");   

}
}
Coordinator
Apr 28, 2012 at 10:01 AM
  • Check whether the $cell value is scalar, or a rich text object. If the latter, you will need to cast to string or use the rifh text object's getPlainText() method
  • Escape the value of $cell in case it contains quotes (this should always be standard practise when working with databases) or switch to MySQLi or PDO Prepared statements.
  • Use error handling in your code

 

Apr 28, 2012 at 12:30 PM

hi MarkBaker,

thanks for your response. My problem has been resolved for insert data into database. But another problem has been occored. I have an excel file with field 'Description(May be more than 1000 characters in each cell)' including 248 rows right now. When i try to insert it into database table insert then first 92 lines executed into database. Can you assist me regard this matter why phpExcel is limiting this. In near future may be excel rows should be more than 50000. Please tell me how can i handle large files data. Thanks 

Apr 28, 2012 at 2:45 PM

I got it. The description cell contains on html elements i.e{<table>, <p>, <b> font so on....}. Each excel cell is heavy therefore i need proper method which will deal with html. Is there any function or method in phpexcel that will handle this html cell data and take it from excel and insert into database from start to end? If yes please give me at least one example. Thanks