Tuesday, 31 October 2017

Import CSV Data into Mysql in Codeigniter



We know how to import CSV Data into Mysql table using PHP script but now in this post we have discuss this topic into Codeigniter Framework. So Here we have learn how to Import Data from CSV file to Mysql Database table in Codeigniter Framework. Importing of data in Codeigniter framework required different coding style then simple PHP script because it is an MVC (Model View Controller) framework. For do this task we have create three different file into Controllers, models and views folder. First request will be received at controller folder then it will send request to models files and it will do required database operation and return data to controller method and after this it will print data in view files. This way MVC framework will execute code.



Importing of Bulk Data through CSV file is one of the required feature of any web application and it will reduce time to insert data one by one. Here we want to import data from CSV file to Mysql database in Codeigniter framework. There is no any library for CSV file in Codeigniter, but we have search on internet and we have find one library 'csvimport' which is developed for import of CSV data in Codeigniter framework. So we have use 'csvimport' library for importing of data from CSV file to Mysql database in Codeigniter Framework. This library will required only CSV file location and by using this get_array() method of 'csvimport' library we can easily convert CSV data to php array in controller file code. After getting CSV file data into PHP array we can easily play with that data.

If you have import bulk data from CSV file and if you have use Codeigniter framework for web application development then you can use Codeigniter Database library insert_batch() method. By using this method we can execute multiple Insert data query in single query execution. So, by using this method we can import large amount of data in a very short time because by using 'csvimport' library we have got array of CSV file data and after this we have use insert_batch() we have directly import all data in single query execution. So this is one benefit of Codeigniter framework if you have import large data. Here we have also use Ajax Jquery so all operation has been done without refresh of webpage. Here we have upload CSV file by using Ajax Jquery FormData object. So this way we can Import Data from CSV file in Codeigniter Framework by using Ajax JQuery.









Source Code


controllers - Csv_import.php



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

class Csv_import extends CI_Controller {
 
 public function __construct()
 {
  parent::__construct();
  $this->load->model('csv_import_model');
  $this->load->library('csvimport');
 }

 function index()
 {
  $this->load->view('csv_import');
 }

 function load_data()
 {
  $result = $this->csv_import_model->select();
  $output = '
   <h3 align="center">Imported User Details from CSV File</h3>
        <div class="table-responsive">
         <table class="table table-bordered table-striped">
          <tr>
           <th>Sr. No</th>
           <th>First Name</th>
           <th>Last Name</th>
           <th>Phone</th>
           <th>Email Address</th>
          </tr>
  ';
  $count = 0;
  if($result->num_rows() > 0)
  {
   foreach($result->result() as $row)
   {
    $count = $count + 1;
    $output .= '
    <tr>
     <td>'.$count.'</td>
     <td>'.$row->first_name.'</td>
     <td>'.$row->last_name.'</td>
     <td>'.$row->phone.'</td>
     <td>'.$row->email.'</td>
    </tr>
    ';
   }
  }
  else
  {
   $output .= '
   <tr>
       <td colspan="5" align="center">Data not Available</td>
      </tr>
   ';
  }
  $output .= '</table></div>';
  echo $output;
 }

 function import()
 {
  $file_data = $this->csvimport->get_array($_FILES["csv_file"]["tmp_name"]);
  foreach($file_data as $row)
  {
   $data[] = array(
    'first_name' => $row["First Name"],
          'last_name'  => $row["Last Name"],
          'phone'   => $row["Phone"],
          'email'   => $row["Email"]
   );
  }
  $this->csv_import_model->insert($data);
 }
 
  
}


models - Csv_import_model.php



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

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


views - csv_import.php



<html>
<head>
    <title>How to Import CSV Data into Mysql using Codeigniter</title>
    
 <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
    
</head>
<body>
 <div class="container box">
  <h3 align="center">How to Import CSV Data into Mysql using Codeigniter</h3>
  <br />

  <form method="post" id="import_csv" enctype="multipart/form-data">
   <div class="form-group">
    <label>Select CSV File</label>
    <input type="file" name="csv_file" id="csv_file" required accept=".csv" />
   </div>
   <br />
   <button type="submit" name="import_csv" class="btn btn-info" id="import_csv_btn">Import CSV</button>
  </form>
  <br />
  <div id="imported_csv_data"></div>
 </div>
</body>
</html>

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

 load_data();

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

 $('#import_csv').on('submit', function(event){
  event.preventDefault();
  $.ajax({
   url:"<?php echo base_url(); ?>csv_import/import",
   method:"POST",
   data:new FormData(this),
   contentType:false,
   cache:false,
   processData:false,
   beforeSend:function(){
    $('#import_csv_btn').html('Importing...');
   },
   success:function(data)
   {
    $('#import_csv')[0].reset();
    $('#import_csv_btn').attr('disabled', false);
    $('#import_csv_btn').html('Import Done');
    load_data();
   }
  })
 });
 
});
</script>



--
-- Database: `testing`
--

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

--
-- Table structure for table `tbl_user`
--

CREATE TABLE IF NOT EXISTS `tbl_user` (
  `id` int(11) NOT NULL,
  `first_name` varchar(250) NOT NULL,
  `last_name` varchar(250) NOT NULL,
  `phone` varchar(30) NOT NULL,
  `email` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_user`
--
ALTER TABLE `tbl_user`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_user`
--
ALTER TABLE `tbl_user`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

0 comments:

Post a Comment