Friday 6 April 2018

How to Import Excel Data into Mysql Database using Codeigniter




In this post, we have discuss how can we import Excel Sheet data into Mysql database in Codeigniter framework. For this things we will use third party PHPExcel library in Codeigniter for Import Excel sheet data into Mysql Database by using Ajax Jquery. Importing for Excel sheet data into Mysql database that means first we will read excel sheet data and then after we will store into PHP array and lastly we will perform batch operation that means in a single query execution we will insert multiple data. In Codeigniter framework there is one insert_batch() query builder method by using this method we can insert multiple data in single query execution. This all process will cover step by step in Webslesson youtube channel.

How to use PHPExcel Library in Codeigniter framework, so first we have to download PHPExcel Library and put into libraries folder. In that folder we have to create one more Excel.php class. We will extends all property of PHPExcel class library into this class.











Excel.php



<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');
require_once('PHPExcel.php');

class Excel extends PHPExcel
{
 public function __construct()
 {
  parent::__construct();
 }
}

?>


Here we have make Excel library and this library we will load this library in controller by simply load library method. After this we have to make Excel_import controller for handle http request for Import an Excel file into Mysql using Controller. Under this controller we have make three method like index(), fetch() and import(). index() method will load view file on browser. fetch method has been use by ajax request for fetching customer table data on web page in html table. And last import() method is used for import excel sheet data and read excel sheet file and convert that data into php array and lastly by using codeigniter insert batch method insert or import multiple data in single query execution.

Controllers - Excel_import_model.php



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

class Excel_import extends CI_Controller
{
 public function __construct()
 {
  parent::__construct();
  $this->load->model('excel_import_model');
  $this->load->library('excel');
 }

 function index()
 {
  $this->load->view('excel_import');
 }
 
 function fetch()
 {
  $data = $this->excel_import_model->select();
  $output = '
  <h3 align="center">Total Data - '.$data->num_rows().'</h3>
  <table class="table table-striped table-bordered">
   <tr>
    <th>Customer Name</th>
    <th>Address</th>
    <th>City</th>
    <th>Postal Code</th>
    <th>Country</th>
   </tr>
  ';
  foreach($data->result() as $row)
  {
   $output .= '
   <tr>
    <td>'.$row->CustomerName.'</td>
    <td>'.$row->Address.'</td>
    <td>'.$row->City.'</td>
    <td>'.$row->PostalCode.'</td>
    <td>'.$row->Country.'</td>
   </tr>
   ';
  }
  $output .= '</table>';
  echo $output;
 }

 function import()
 {
  if(isset($_FILES["file"]["name"]))
  {
   $path = $_FILES["file"]["tmp_name"];
   $object = PHPExcel_IOFactory::load($path);
   foreach($object->getWorksheetIterator() as $worksheet)
   {
    $highestRow = $worksheet->getHighestRow();
    $highestColumn = $worksheet->getHighestColumn();
    for($row=2; $row<=$highestRow; $row++)
    {
     $customer_name = $worksheet->getCellByColumnAndRow(0, $row)->getValue();
     $address = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
     $city = $worksheet->getCellByColumnAndRow(2, $row)->getValue();
     $postal_code = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
     $country = $worksheet->getCellByColumnAndRow(4, $row)->getValue();
     $data[] = array(
      'CustomerName'  => $customer_name,
      'Address'   => $address,
      'City'    => $city,
      'PostalCode'  => $postal_code,
      'Country'   => $country
     );
    }
   }
   $this->excel_import_model->insert($data);
   echo 'Data Imported successfully';
  } 
 }
}

?>


Below you we have describe Excel_import_model class in models folder for handle database operation for importing or inserting excel sheet data into mysql database in codeigniter framework. In this we have make two select() and insert() method. select() method is used fro fetch whole customer data and return query execution result while insert() method is for insert multipe data in single query execution.

Models - Excel_import_model.php



<?php
class Excel_import_model extends CI_Model
{
 function select()
 {
  $this->db->order_by('CustomerID', 'DESC');
  $query = $this->db->get('tbl_customer');
  return $query;
 }

 function insert($data)
 {
  $this->db->insert_batch('tbl_customer', $data);
 }
}


Lastly we have discuss excel_import.php view file which an output file on browser. From this file user can select excel file for import data into mysql. This file has been load by index() method of Excel_import controller. Under this file we have first make jquery function in which we have use ajax for fetch data from customer table and display on web page in HTML table formate. So when page has been load on web page then it will display latest customer table data on web page. After this we have make one form for import excel sheet data. Here we have use Ajax for import excel sheet data, so when we have submit form data to server then it will import selected excel sheet data into mysql database. When we have submit form data with selected file then it will send request to import() method of Excel_import model and it will import Excel sheet data into mysql table by using PHPExcel library in codeigniter frameowork. If you want complete source code file then you can get by clicking on below link.


<!DOCTYPE html>
<html>
<head>
 <title>How to Import Excel Data into Mysql in Codeigniter</title>
 <link rel="stylesheet" href="<?php echo base_url(); ?>asset/bootstrap.min.css" />
 <script src="<?php echo base_url(); ?>asset/jquery.min.js"></script>
</head>

<body>
 <div class="container">
  <br />
  <h3 align="center">How to Import Excel Data into Mysql in Codeigniter</h3>
  <form method="post" id="import_form" enctype="multipart/form-data">
   <p><label>Select Excel File</label>
   <input type="file" name="file" id="file" required accept=".xls, .xlsx" /></p>
   <br />
   <input type="submit" name="import" value="Import" class="btn btn-info" />
  </form>
  <br />
  <div class="table-responsive" id="customer_data">

  </div>
 </div>
</body>
</html>

<script>
$(document).ready(function(){

 load_data();

 function load_data()
 {
  $.ajax({
   url:"<?php echo base_url(); ?>excel_import/fetch",
   method:"POST",
   success:function(data){
    $('#customer_data').html(data);
   }
  })
 }

 $('#import_form').on('submit', function(event){
  event.preventDefault();
  $.ajax({
   url:"<?php echo base_url(); ?>excel_import/import",
   method:"POST",
   data:new FormData(this),
   contentType:false,
   cache:false,
   processData:false,
   success:function(data){
    $('#file').val('');
    load_data();
    alert(data);
   }
  })
 });

});
</script>

45 comments:

  1. Hello,
    Its giving error in line $object = PHPExcel_IOFactory::load($path); of function function import() of Excel_import controller.
    jQuery error ../excel_import/import 500 (). If I comment out above line onwards, then echo $path = $_FILES["file"]["tmp_name"]; returns the temp path of file.

    Kindly confirm what is the issue.

    ReplyDelete
  2. Hi.
    I have a query.
    in for loop you assign value 2 for $row.
    As mention above
    for($row=2; $row<=$highestRow; $row++)
    {}
    Please tell me why u start it from 2?

    ReplyDelete
  3. Thanks sir you are solved my problem

    ReplyDelete
  4. Replies
    1. i follow of you but working Class 'PHPExcel_IOFactory' not found

      Delete
  5. for the database source and sample excel files. can you share it also? thanks you

    ReplyDelete
  6. i had watch this tutorial, very helpfully, i succeeded to display the data from the db , but the insert part is not working, is someone can provide assistance ?

    ReplyDelete
  7. Your data uploaded has 5 record, but when do upload it only got 4 record. Why it like that?

    ReplyDelete
  8. where testing db? when I download source code, I not found "testing" db file

    ReplyDelete
  9. can you gift me your database pleaseeeee......
    malas cok buat wkwkw

    ReplyDelete
  10. pleasee gift your database, hahahah

    ReplyDelete
  11. how to combine upload excel with progress bar bootstrap on codeigniter

    please helpme...

    ReplyDelete
  12. Unable to load the requested class: Excel

    ReplyDelete
  13. 'ZipArchive' not found in PHPExcel/Reader/Excel2007.php

    ReplyDelete
  14. hii sir thank you so so much this is really best code and this are very help full for me and my team thank you ...

    ReplyDelete
  15. hi sir, if download source code the file was empty..

    ReplyDelete
    Replies
    1. Dear friend add one row in database manually and again run your controllers Definataly run it successfully show tatal data in front end.

      Delete
  16. this code is very usefull thanks sir,
    next code shaire export exel file request.

    ReplyDelete
  17. I have an issue with class excel. can you show me what the inside of PHPExcel.php and IOFactory.php in library ? that might solve my issue, thanks you

    ReplyDelete
    Replies
    1. i think you can download the source code that author has provided

      Delete
  18. You are such a great man thanks

    ReplyDelete
  19. Thankyou very much, work for me! and then, have you tutorial how to export mysql to excel same using code igniter?

    ReplyDelete
  20. grate article, keep it up. If you want to export data you can use this https://www.tutsmake.com/export-data-to-excel-csv-in-codeigniter-using-phpexcel/

    ReplyDelete
  21. His work worked up to a point, its taking the file in but im not seeing any table nor is it entering the data to the database

    ReplyDelete
  22. A PHP Error was encountered

    Severity: Warning
    Message: DOMDocument::loadHTMLFile(): Tag workbook invalid in /tmp/phpPkzDww
    Filename: Reader/HTML.php
    Line Number: 458


    could you help me to resolve this

    ReplyDelete
  23. Thanks. It works perfectly. I wanted to insert big data but I couldn't . I increased $batch_size but it didn't work. Can you help me?

    ReplyDelete
  24. How to settle my error???
    Fatal error: Call to undefined method Main_model::fetch_data() in C:\xampp\htdocs\tutorial\how-to-import-excel-file-in-codeigniter\application\controllers\main.php on line 9

    ReplyDelete
    Replies
    1. Did you find solution to this error? I am having same error

      Delete
    2. what mean by PhpExecl.php

      Delete
    3. change route to this $route['default_controller'] = 'Excel_import';

      Delete
  25. Friend, I can not insert date from Excel to SQL. In SQL, it show 000-00-00
    Can you help me?

    ReplyDelete
  26. How to settle my error???
    Fatal error: Call to undefined method Main_model::fetch_data() in C:\xampp\htdocs\tutorial\how-to-import-excel-file-in-codeigniter\application\controllers\main.php on line 9

    ReplyDelete
  27. How to settle my error???
    Fatal error: Call to undefined method Main_model::fetch_data() in C:\xampp\htdocs\tutorial\how-to-import-excel-file-in-codeigniter\application\controllers\main.php on line 9

    ReplyDelete
  28. Status Code: 500 Internal Server Error online server

    ReplyDelete
  29. Type: PHPExcel_Reader_Exception

    Message: ZipArchive library is not enabled

    ReplyDelete
  30. i am unable to get the cell value in $customer_name got the whole row data

    ReplyDelete
  31. how can i store data into database when csv don't have comma seperation??

    ReplyDelete