Import/Upload Excel file into MySQL using Codeigniter

Using Excel file you can store all the data and import the Excel file data into the database at once using MYSQL and Codeigniter.Import Excel into MySQL helps to save the user time and avoid repetitive work.In this tutorial, We will explain how to import an Excel into MySQL database using CodeIgniter.

Also Read : Import Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL

First, We need to download PHPExcel Library. Then extract PHPExcel Library

Step 1: Extract PHPExcel Library

Note: Copy and Paste inside “application/third_party” folder.

Step 2: Create file

Create a file named Excel.php inside “application/libraries” folder.

<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');
/*
 *  ======================================= 
 *  Author     : Team Tech Arise 
 *  License    : Protected 
 *  Email      : info@techarise.com 
 * 
 *  ======================================= 
 */
require_once APPPATH . "/third_party/PHPExcel.php";
class Excel extends PHPExcel {
    public function __construct() {
        parent::__construct();
    }
}
?>

<?php

if (!defined('BASEPATH')) exit('No direct script access allowed');

* =======================================

* Author : Team Tech Arise

* License : Protected

* Email : info@techarise.com

* =======================================

require_once APPPATH . "/third_party/PHPExcel.php";

class Excel extends PHPExcel {

public function __construct() {

parent::__construct();

}

Step 3: Create Database and Table

For this tutorial, you need a MySQL database with the following table:

<?php
//Table structure for table import
CREATE TABLE `import` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `first_name` varchar(100) NOT NULL COMMENT 'First Name',
  `last_name` varchar(100) NOT NULL COMMENT 'Last Name',
  `email` varchar(255) NOT NULL COMMENT 'Email Address',
  `dob` varchar(20) NOT NULL COMMENT 'Date of Birth',
  `contact_no` int(11) NOT NULL COMMENT 'Contact No',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=1;
?>

<?php

//Table structure for table import

CREATE TABLE `import` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',

`first_name` varchar(100) NOT NULL COMMENT 'First Name',

`last_name` varchar(100) NOT NULL COMMENT 'Last Name',

`email` varchar(255) NOT NULL COMMENT 'Email Address',

`dob` varchar(20) NOT NULL COMMENT 'Date of Birth',

`contact_no` int(11) NOT NULL COMMENT 'Contact No',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=1;

Step 4: Create Controller and load class

Syntax: Load “excel” class in controller.

<?php
    // load library
    $this->load->library('excel');
 ?>

<?php

// load library

$this->load->library('excel');

Create a controller file like contactus.php inside “application/controllers” folder.

<?php
/**
 * Description of Import Controller
 *
 * @author TechArise Team
 *
 * @email  info@techarise.com
 */
if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Import extends CI_Controller {

    public function __construct() {
        parent::__construct();
        $this->load->model('Import_model', 'import');
    }

    // upload xlsx|xls file
    public function index() {
        $data['page'] = 'import';
        $data['title'] = 'Import XLSX | TechArise';
        $this->load->view('import/index', $data);
    }
    // import excel data
    public function save() {
        $this->load->library('excel');
        
        if ($this->input->post('importfile')) {
            $path = ROOT_UPLOAD_IMPORT_PATH;

            $config['upload_path'] = $path;
            $config['allowed_types'] = 'xlsx|xls|jpg|png';
            $config['remove_spaces'] = TRUE;
            $this->upload->initialize($config);
            $this->load->library('upload', $config);
            if (!$this->upload->do_upload('userfile')) {
                $error = array('error' => $this->upload->display_errors());
            } else {
                $data = array('upload_data' => $this->upload->data());
            }
            
            if (!empty($data['upload_data']['file_name'])) {
                $import_xls_file = $data['upload_data']['file_name'];
            } else {
                $import_xls_file = 0;
            }
            $inputFileName = $path . $import_xls_file;
            try {
                $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
                $objReader = PHPExcel_IOFactory::createReader($inputFileType);
                $objPHPExcel = $objReader->load($inputFileName);
            } catch (Exception $e) {
                die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME)
                        . '": ' . $e->getMessage());
            }
            $allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
            
            $arrayCount = count($allDataInSheet);
            $flag = 0;
            $createArray = array('First_Name', 'Last_Name', 'Email', 'DOB', 'Contact_NO');
            $makeArray = array('First_Name' => 'First_Name', 'Last_Name' => 'Last_Name', 'Email' => 'Email', 'DOB' => 'DOB', 'Contact_NO' => 'Contact_NO');
            $SheetDataKey = array();
            foreach ($allDataInSheet as $dataInSheet) {
                foreach ($dataInSheet as $key => $value) {
                    if (in_array(trim($value), $createArray)) {
                        $value = preg_replace('/\s+/', '', $value);
                        $SheetDataKey[trim($value)] = $key;
                    } else {
                        
                    }
                }
            }
            $data = array_diff_key($makeArray, $SheetDataKey);
           
            if (empty($data)) {
                $flag = 1;
            }
            if ($flag == 1) {
                for ($i = 2; $i <= $arrayCount; $i++) {
                    $addresses = array();
                    $firstName = $SheetDataKey['First_Name'];
                    $lastName = $SheetDataKey['Last_Name'];
                    $email = $SheetDataKey['Email'];
                    $dob = $SheetDataKey['DOB'];
                    $contactNo = $SheetDataKey['Contact_NO'];
                    $firstName = filter_var(trim($allDataInSheet[$i][$firstName]), FILTER_SANITIZE_STRING);
                    $lastName = filter_var(trim($allDataInSheet[$i][$lastName]), FILTER_SANITIZE_STRING);
                    $email = filter_var(trim($allDataInSheet[$i][$email]), FILTER_SANITIZE_EMAIL);
                    $dob = filter_var(trim($allDataInSheet[$i][$dob]), FILTER_SANITIZE_STRING);
                    $contactNo = filter_var(trim($allDataInSheet[$i][$contactNo]), FILTER_SANITIZE_STRING);
                    $fetchData[] = array('first_name' => $firstName, 'last_name' => $lastName, 'email' => $email, 'dob' => $dob, 'contact_no' => $contactNo);
                }              
                $data['employeeInfo'] = $fetchData;
                $this->import->setBatchImport($fetchData);
                $this->import->importData();
            } else {
                echo "Please import correct file";
            }
        }
        $this->load->view('import/display', $data);
        
    }
}
?>

100

101

102

103

104

105

<?php

/**

* Description of Import Controller

* @author TechArise Team

* @email info@techarise.com

if (!defined('BASEPATH'))

exit('No direct script access allowed');

class Import extends CI_Controller {

public function __construct() {

parent::__construct();

$this->load->model('Import_model', 'import');

}

// upload xlsx|xls file

public function index() {

$data['page'] = 'import';

$data['title'] = 'Import XLSX | TechArise';

$this->load->view('import/index', $data);

}

// import excel data

public function save() {

$this->load->library('excel');

if ($this->input->post('importfile')) {

$path = ROOT_UPLOAD_IMPORT_PATH;

$config['upload_path'] = $path;

$config['allowed_types'] = 'xlsx|xls|jpg|png';

$config['remove_spaces'] = TRUE;

$this->upload->initialize($config);

$this->load->library('upload', $config);

if (!$this->upload->do_upload('userfile')) {

$error = array('error' => $this->upload->display_errors());

} else {

$data = array('upload_data' => $this->upload->data());

}

if (!empty($data['upload_data']['file_name'])) {

$import_xls_file = $data['upload_data']['file_name'];

} else {

$import_xls_file = 0;

}

$inputFileName = $path . $import_xls_file;

try {

$inputFileType = PHPExcel_IOFactory::identify($inputFileName);

$objReader = PHPExcel_IOFactory::createReader($inputFileType);

$objPHPExcel = $objReader->load($inputFileName);

} catch (Exception $e) {

die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME)

. '": ' . $e->getMessage());

}

$allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);

$arrayCount = count($allDataInSheet);

$flag = 0;

$createArray = array('First_Name', 'Last_Name', 'Email', 'DOB', 'Contact_NO');

$makeArray = array('First_Name' => 'First_Name', 'Last_Name' => 'Last_Name', 'Email' => 'Email', 'DOB' => 'DOB', 'Contact_NO' => 'Contact_NO');

$SheetDataKey = array();

foreach ($allDataInSheet as $dataInSheet) {

foreach ($dataInSheet as $key => $value) {

if (in_array(trim($value), $createArray)) {

$value = preg_replace('/\s+/', '', $value);

$SheetDataKey[trim($value)] = $key;

} else {

}

$data = array_diff_key($makeArray, $SheetDataKey);

if (empty($data)) {

$flag = 1;

}

if ($flag == 1) {

for ($i = 2; $i <= $arrayCount; $i++) {

$addresses = array();

$firstName = $SheetDataKey['First_Name'];

$lastName = $SheetDataKey['Last_Name'];

$email = $SheetDataKey['Email'];

$dob = $SheetDataKey['DOB'];

$contactNo = $SheetDataKey['Contact_NO'];

$firstName = filter_var(trim($allDataInSheet[$i][$firstName]), FILTER_SANITIZE_STRING);

$lastName = filter_var(trim($allDataInSheet[$i][$lastName]), FILTER_SANITIZE_STRING);

$email = filter_var(trim($allDataInSheet[$i][$email]), FILTER_SANITIZE_EMAIL);

$dob = filter_var(trim($allDataInSheet[$i][$dob]), FILTER_SANITIZE_STRING);

$contactNo = filter_var(trim($allDataInSheet[$i][$contactNo]), FILTER_SANITIZE_STRING);

$fetchData[] = array('first_name' => $firstName, 'last_name' => $lastName, 'email' => $email, 'dob' => $dob, 'contact_no' => $contactNo);

}

$data['employeeInfo'] = $fetchData;

$this->import->setBatchImport($fetchData);

$this->import->importData();

} else {

echo "Please import correct file";

}

$this->load->view('import/display', $data);

}

Step 5: Create Model

Create a model file named Import_model.php inside “application/models” folder.

<?php
/**
 * Description of Import Model
 *
 * @author TechArise Team
 *
 * @email  info@techarise.com
 */
if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Import_model extends CI_Model {

    private $_batchImport;

    public function setBatchImport($batchImport) {
        $this->_batchImport = $batchImport;
    }

    // save data
    public function importData() {
        $data = $this->_batchImport;
        $this->db->insert_batch('import', $data);
    }
    // get employee list
    public function employeeList() {
        $this->db->select(array('e.id', 'e.first_name', 'e.last_name', 'e.email', 'e.dob', 'e.contact_no'));
        $this->db->from('import as e');
        $query = $this->db->get();
        return $query->result_array();
    }

}

?>

<?php

/**

* Description of Import Model

* @author TechArise Team

* @email info@techarise.com

if (!defined('BASEPATH'))

exit('No direct script access allowed');

class Import_model extends CI_Model {

private $_batchImport;

public function setBatchImport($batchImport) {

$this->_batchImport = $batchImport;

}

// save data

public function importData() {

$data = $this->_batchImport;

$this->db->insert_batch('import', $data);

}

// get employee list

public function employeeList() {

$this->db->select(array('e.id', 'e.first_name', 'e.last_name', 'e.email', 'e.dob', 'e.contact_no'));

$this->db->from('import as e');

$query = $this->db->get();

return $query->result_array();

}

Step 6: Create views (Upload Excel file)

Create a views file named index.php inside “application/views/import” folder.

    <div class="row">
    <div class="col-lg-12">
        <h1>Lawyers <small>Overview</small></h1>
        <ol class="breadcrumb">
            <li class="active"><i class="fa fa-list"></i> Import Members</li>
        </ol>            
    </div>
</div><!-- /.row -->
<?php
$output = '';
$output .= form_open_multipart('import/save');
$output .= '<div class="row">';
$output .= '<div class="col-lg-12 col-sm-12"><div class="form-group">';
$output .= form_label('Import Lawyers', 'image');
$data = array(
    'name' => 'userfile',
    'id' => 'userfile',
    'class' => 'form-control filestyle',
    'value' => '',
    'data-icon' => 'false'
);
$output .= form_upload($data);
$output .= '</div> <span style="color:red;">*Please choose an Excel file(.xls or .xlxs) as Input</span></div>';
$output .= '<div class="col-lg-12 col-sm-12"><div class="form-group text-right">';
$data = array(
    'name' => 'importfile',
    'id' => 'importfile-id',
    'class' => 'btn btn-primary',
    'value' => 'Import',
);
$output .= form_submit($data, 'Import Data');
$output .= '</div>
                        </div></div>';
$output .= form_close();
echo $output;
?>

<h1>Lawyers <small>Overview</small></h1>

<li class="active"><i class="fa fa-list"></i> Import Members</li>

</ol>

</div>

</div>

<?php

$output = '';

$output .= form_open_multipart('import/save');

$output .= '<div class="row">';

$output .= '<div class="col-lg-12 col-sm-12"><div class="form-group">';

$output .= form_label('Import Lawyers', 'image');

$data = array(

'name' => 'userfile',

'id' => 'userfile',

'class' => 'form-control filestyle',

'value' => '',

'data-icon' => 'false'

);

$output .= form_upload($data);

$output .= '</div> <span style="color:red;">*Please choose an Excel file(.xls or .xlxs) as Input</span></div>';

$output .= '<div class="col-lg-12 col-sm-12"><div class="form-group text-right">';

$data = array(

'name' => 'importfile',

'id' => 'importfile-id',

'class' => 'btn btn-primary',

'value' => 'Import',

);

$output .= form_submit($data, 'Import Data');

$output .= '</div>

</div></div>';

$output .= form_close();

echo $output;

Step 7: Create views (Display Excel data)

Create a views file named display.php inside “application/views/import” folder.

<div class="table-responsive">
    <table class="table table-hover tablesorter">
        <thead>
            <tr>
                <th class="header">First Name</th>
                <th class="header">Last Name</th>                           
                <th class="header">Email</th>                      
                <th class="header">DOB</th>
                <th class="header">Contact Name</th>
            </tr>
        </thead>
        <tbody>
            <?php
            if (isset($employeeInfo) && !empty($employeeInfo)) {
                foreach ($employeeInfo as $key => $element) {
                    ?>
                    <tr>
                        <td><?php echo $element['first_name']; ?></td>   
                        <td><?php echo $element['last_name']; ?></td> 
                        <td><?php echo $element['email']; ?></td>                       
                        <td><?php echo $element['dob']; ?></td>
                        <td><?php echo $element['contact_no']; ?></td>
                    </tr>
                    <?php
                }
            } else {
                ?>
                <tr>
                    <td colspan="5">There is no employee.</td>    
                </tr>
            <?php } ?>

        </tbody>
    </table>
</div>

<thead>

<tr>

<th class="header">First Name</th>

<th class="header">Email</th>

<th class="header">Contact Name</th>

</tr>

</thead>

<tbody>

<?php

if (isset($employeeInfo) && !empty($employeeInfo)) {

foreach ($employeeInfo as $key => $element) {

<tr>

</tr>

<?php

}

} else {

<tr>

<td colspan="5">There is no employee.</td>

</tr>

<?php } ?>

</tbody>

</table>

</div>

Also Read : Import Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL

Demo Download