Bulk insert records in database from excel

Question

I am developing a component from which user can upload excel sheet and the data must be saved into database. When excel sheet rows are 250 then insertion works fine but when the rows are 2500 or more it gives a blank page with no error and rows are not inserted completely into the database. I am using PHPExcel library for importing excel sheets. Following is the code:

1. controllers/fileuploads.php:

   public function uploadData($fileData, $data,$tablename){
   
   //Validate file by checking if excel sheet is uploaded
   $isValid = $this->validateFileType($fileData);
   
   if($isValid){
   
       $inputFilename = $fileData['excelfile']['tmp_name'];
       try {
           $objPHPExcel    = PHPExcel_IOFactory::load($inputFilename); //read the spreadsheet workbook
       } catch (Exception $e) {
           die('Error loading file "'.pathinfo($inputFilename,PATHINFO_BASENAME).'": '.$e->getMessage());
       }
   
       //  Get worksheet dimensions
       $sheet = $objPHPExcel->getSheet(0);
       $highestRow = $sheet->getHighestRow();
       $highestColumn = $sheet->getHighestColumn();
       for ($row = 4; $row <= $highestRow; $row++){
         // Read a row of data into an array
         $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row);
   
         if(!empty($rowData[0][0])){ //if row is null
              //  Insert row data array into database
              $isInserted = $this->insertData($rowData, $data);
         }
   
        }// excel for loop ends
   
      if($isInserted){
       //redirect after importing the data from excel into database
       $this->setRedirect(JRoute::_('index.php?option=com_mycomponent&view=fileuploads', false), 'File uploaded successfully','message');
      }
   
   }//if($isValid)
   }
   

2. controllers/fileuploads - insertData method: this method calls different model methods based on which section's sheet is uploaded. Ex. Section Test will call a method getInsertSectionTest() from the model. There are 6 different sections.

3. models/fileupload.php: Following is the code for inserting data

   public function getInsertSectionTest($rowData, $data){
     $objData = new stdClass();
     $objData->section_id = $data['section'][0];
     $objData->category = str_replace("'","",$rowData[0][0]);
     $objData->subcategory = str_replace("'","",$rowData[0][6]);
     $objData->year = $data['year'];
     $objData->number_type = $rowData[0][7];
     .
     . // and so on
     .
     $objData->region_northwest = $rowData[0][43];
     $objData->region_southwest = $rowData[0][44];
     $objData->region_unknown = $rowData[0][45];
   
     $insertResult = JFactory::getDbo()->insertObject('#__testsection_tablename', $objData);
   
     if( $insertResult){
       return true;
     }
     else{
       $this->setRedirect(JRoute::_('index.php?option=com_mycomponent&view=fileuploads', false), 'Data could not be imported. Please try again' ,'error');
       return false;
     }
   }
   

Kindly, let me know how more than 2000 rows be inserted at once. I also referred this question but not able to form the array of values to be inserted.

Answer 1

I solved the issue. Problem was that when insert query was called in a loop 2000 times, deadlock occurred and returned a blank page. The solution to this is to construct one single insert query and then insert the values. Following is the code I added in model file -

Instead of calling insert method in loop I formed the insert values array:

$getValues = array();

for ($row = 4; $row <= $highestRow; $row++){
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row);
    if(!empty($rowData[0][0])){ //if row is null
        //  Insert row data array into database
        $getValues[] = $this->getValuesModel($rowData, $data);
    }

}// excel for loop ends

$columns = $this->getColumns($data); //call columns method to fetch different section's columns

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->insert($db->quoteName($tablename));
$query->columns($columns);
$query->values($getValues);
$db->setQuery($query);
$isInserted = $db->query();

if($isInserted){
    //redirect after importing the data from excel into database
    $this->setRedirect(JRoute::_('index.php?option=com_componentname&view=fileuploads', false), 'File uploaded successfully','message');
}

For complete code and files, you refer Github.