Dynamically creating a db table from excel file

Topics: Developer Forum, User Forum
Dec 9, 2011 at 12:20 PM

Hello. I 'm trying to  create a db table from excel file with phpexcel.Here is the concept:

I first try to read the headers from the first row(They are always in this row).

Then i iterate over the worksheet to discover each column's datatype.I then create the query for the creation of the table and finally, i am looping through each row (below the 1 one) to insert the values

to the created table.

The problem is i cant get the code to work.

I am getting  an error of max_execution time exceeded in PHPExcel/PHPExcel/Worksheet.php on line 998

 The code i am using is:

 

----------------------------------------------------------------------------------

 

require_once 'PHPExcel/Autoloader.php';

require_once 'PHPExcel/IOFactory.php';

require_once 'PHPExcel.php';

 

/* $cm=PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;   

   PHPExcel_Settings::setCacheStorageMethod($cm);
   $cm=PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;   

   $cs=array('memoryCacheSize'=>'50MB');     

    PHPExcel_Settings::setCacheStorageMethod($cm,$cs);*/     

 

    PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);   

    $objPHPExcel = PHPExcel_IOFactory::load("Untitled.xls");     

    foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {  

   $highestRow = $worksheet->getHighestRow();

     $highestColumn = $worksheet->getHighestColumn();    

   $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

     while($row=1):  

  for ($col = 0; $col < $highestColumnIndex;  $col++){  

  $cell = $worksheet->getCellByColumnAndRow($col, $row);  

  $hvalue = $cell->getValue();    

  $hvalues=array();  

  $hvalues[]=$hvalue;           }

  endwhile;

 $mkimploded=implode(",",$hvalues);     

       }

 require_once 'PHPExcel/Autoloader.php';             

require_once ('config.inc.php');

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {    

$highestRow = $worksheet->getHighestRow();  

 $highestColumn = $worksheet->getHighestColumn();    

 $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);   

 for ($col = 0; $col< $highestColumnIndex; $col++) {   //read file on a per column basis;vertically  

 for ($row = 2; $row <= $highestRow; $row++) {     

 $cell = $worksheet->getCellByColumnAndRow($col, $row);  

 $val = $cell->getValue();      

 $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);    

  $columndatatype=array();     

   $columndatatype[]=$datatype;   

 }}}                  

 

function array_most_common($input){

$counted = array_count_values($input);

 $rcounted=arsort($counted);

return(key($rcounted));}

 

   switch ($dbcoldatatype)        {    

    case "str":    

    if(array_most_common($columndatatype)=="str")    

    $dbcoldatatype="varchar(150)";    

    break;    

    case "s":    

    if(array_most_common($columndatatype)=="s")  

      $dbcoldatatype="varchar(150)";      

  break;      

  case "f":    

    if(array_most_common($columndatatype)=="f")    

    $dbcoldatatype="varchar(150)";    

    break;    

    case "n":    

    if(array_most_common($columndatatype)=="n")    

    $dbcoldatatype="numeric";    

    break;      

  case "b":      

  if(array_most_common($columndatatype)=="b")    

    $dbcoldatatype="varchar(150)";    

    break;      

  case "null":

        if(array_most_common($columndatatype)=="null")  

      $dbcoldatatype="varchar(150)";    

    break;    

    case "inlineStr":  

      if(array_most_common($columndatatype)=="inlineStr")    

    $dbcoldatatype="varchar(150)";

          break;  

      case "e":    

    if(array_most_common($columndatatype)=="e")    

    $dbcoldatatype="varchar(150)";    

    break;    

    default:    

    break;    

   }             

 require_once"mysqli_connect.php";     

require_once 'PHPExcel/Autoloader.php';    

   $colvaltype=array_combine($hvalues,$dbcoldatatype);     

                          $str="";   

                            $k="";    

   foreach($colvaltype as $key->$val){    

    $str.=$key." ".$val.",";               

                        $k.=$key.",";       

           $q="CREATE TABLE username (id int auto_increment not null primary key,$str)";                                    

   $q1=mysqli_query($dbc,$q);                                    

         $question='DESC username';           

    $result =@mysqli_query($question,$dbc);
       if(!mysqli_errno()==1146){         

  foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {   

    $highestRow = $worksheet->getHighestRow();

 $highestColumn = $worksheet->getHighestColumn();

  $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);   

 for ($row = 2; $row <= $highestRow; $row++) {//in this iteration we read the file horizontally per row;     

  for ($col = 0; $col < $highestColumnIndex; $col++) {       

  $cell = $worksheet->getCellByColumnAndRow($col, $row);      

 $value = $cell->getValue();                 

$values=array();         

 $values[]=$value;             

$imploded=implode(",",$values); }

  //while the values of each row are parsed we insert them into the table 

$query='INSERT INTO username($k) VALUES($imploded)';

$imp=mysqli_query($dbc,$query);//TODO:check if the rows were successfully inserted     

 }              } }

else echo"The table was not created";

        }         mysqli_close($dbc);

 

-------------------------------------------------------------------------------

 

 Any suggestions? 

Coordinator
Dec 9, 2011 at 12:35 PM
Edited Dec 9, 2011 at 12:35 PM

Initialise your arrays before the loop, not immediately before pushing values to them. e.g.

for ($row = 2; $row <= $highestRow; $row++) { 
    $cell = $worksheet->getCellByColumnAndRow($col, $row); 
    $val = $cell->getValue(); 
    $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val); 
    $columndatatype=array();
    $columndatatype[]=$datatype;
}

will reset the array to empty every time you loop, so only the last column value will ever be in the array at the end of the loop... try

$columndatatype=array();
for ($row = 2; $row <= $highestRow; $row++) {
    $cell = $worksheet->getCellByColumnAndRow($col, $row); 
    $val = $cell->getValue(); 
    $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val); 
    $columndatatype[]=$datatype;
}

and for the equivalent loops as well

 

Dec 10, 2011 at 10:09 AM
Edited Dec 10, 2011 at 10:24 AM

Thanks for replying. Such a silly mistake. Now when i run the code i get 100% load on both cores and 4 GBS of RAM & 1 of the 2 GB of swap  filled. I also get an error of:

Allowed memory size of -1673527296 bytes exhausted on line 67 which is when the values are pushed to the array specifically on the first loop

$hvalues[]=$hvalue;   

 

P.S

Although i knew it was going to be a cpu intensive and memory hungry task i did not imagine that it could be that hungry on resources. I actually fed phpexcel with a 10 row and 3 column excel file.

Anyway congrats for a great object oriented project to you and to the other developers. Its a brilliant idea.

I should also mention that i am running latest xampp package with php 5.3.8 i think and php's memory is at 2.5 GB.I applied all the latest patches from phpexcel's svn.