How to Import Excel Data into Mysql Database using PHP CodeIgniter

In this tutorial, we will would love to share with you how to import excel file data in MySql database using PHP CodeIgniter.

Step 1: Create one table like `EMPLOYEE_MASTER_DATA`

CREATE TABLE `EMPLOYEE_MASTER_DATA` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `EMP_NO` varchar(50) DEFAULT NULL,
  `EMP_NAME` varchar(255) DEFAULT NULL,
  `ADDRESS` text DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step 2: Create one file and write below simple HTML code

<form method="post" enctype="multipart/form-data" action="https://app.shinerweb.com/index.php/import_excel/save_entry">
<input type="file" id="excel_file" name="excel_file">
  <input type="submit" value="Upload">
</form>
<br>
<a download href="<?php echo base_url() ?>/uploads/template/Data Upload Template.xlsx">Download template file</a>

Step 3: Write below code in your controller file if you are using simple PHP then skip this code.

function save_entry()
    {
        $this->load->model('import_excel_model');
		$this->import_excel_model->save_entry();
    }

Step 4: You need to create one folder like “uploads” into your project folder.

Step 5: Finally you need to write logic to store excel data into the database.

function save_entry()
	{	
		include_once('excel_reader2.php');
		include_once('SpreadsheetReader.php');
		$mimes = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.oasis.opendocument.spreadsheet','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
		
		//echo $_FILES["excel_file"]["type"];
		//exit;
		
		if(in_array($_FILES["excel_file"]["type"],$mimes)){
			$uploadFilePath = 'uploads/'.basename($_FILES['excel_file']['name']);
			move_uploaded_file($_FILES['excel_file']['tmp_name'], $uploadFilePath);
			$Reader = new SpreadsheetReader($uploadFilePath);
			$totalSheet = count($Reader->sheets());

			/* For Loop for all sheets */
			for($i=0;$i<$totalSheet;$i++){
			  $Reader->ChangeSheet($i);
			  $row_count=0;
			  foreach ($Reader as $Row)
			  {
				if($row_count!=0)
				{
					$EMP_NO = isset($Row[0]) ? $Row[0] : '';
					$EMP_NAME = isset($Row[1]) ? $Row[1] : '';
					$ADDRESS = isset($Row[2]) ? $Row[2] : '';
					
					$data = array(
							'EMP_NO'      => 	$EMP_NO,
							'EMP_NAME'      => 	$EMP_NAME,
							'ADDRESS'      => 	$ADDRESS
							);
					$this->db->insert('EMPLOYEE_MASTER_DATA', $data);
				}
				$row_count++;
				
			  }
			}
			$row_count=$row_count-1;
			
			echo "<script>alert('".$row_count." Record(s) has been inserted! Thank you.') </script>";  	
			echo "<script language=\"javascript\">window.open('https://app.shinerweb.com/index.php/import_excel/', '_self');  </script>";
			
		}
		else
		{
			echo "<script>alert('Please select valid excel file!.') </script>";  	
			echo "<script language=\"javascript\">window.open('https://app.shinerweb.com/index.php/import_excel/', '_self');  </script>";
		}
		
	}

In above code you must have to include below library into your code or your project folder.