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;

19 comments:

  1. Seems to have problems concerning the ajax functions... in the function it notes that it failed to get the resource for load_data I have already set the config as well, not sure what I'm doing wrong but to give you a better picture of what's happening on my end is that the table that shows up from the load_data function does not appear at all and checking the console shows the "failed to get the resource" error.

    ReplyDelete
  2. very helpful and thank you for your effort.

    ReplyDelete
  3. It will be very helpful if you can show us on how to validate the columns in the csv file in the above tutorial.

    ReplyDelete
  4. Thanks it is very very helpful to me.

    ReplyDelete
  5. How to read text file only without using any library using codeigniter

    ReplyDelete
  6. nice but can u provide me csv library please asap

    ReplyDelete
  7. because whene we $this->load->library('csvimport'); this library it show error like this


    An Error Was Encountered

    Unable to load the requested class: Csvimport


    please help me how to solve this error

    ReplyDelete
  8. stuck on importing... status
    any idea? i'm using php7 and CI 3.1.10

    ReplyDelete
  9. sir i want to insert csv data into my database along with some values coming from html page and also i have to perform join operation. sir plz tell me

    ReplyDelete
  10. A PHP Error was encountered
    Severity: Error

    Message: Call to undefined method Main_model::fetch_data()

    Filename: controllers/Main.php

    Line Number: 9

    Backtrace:

    ReplyDelete
  11. 404 Page Not Found
    The page you requested was not found.

    ReplyDelete
  12. It is removing empty data. but i want empty data also

    ReplyDelete
  13. I have a problem with ÆØÅ.
    I come from Denmark and we use the letters in the name. Do some people have an idea for it to upload the letters

    ReplyDelete
  14. Problem width danish ÆØÅ in my name Som can help me. when i upload this csv file so my name like this
    Tordbj¿rn it should stand Tordbjørn

    ReplyDelete
  15. hi where is the library Csvimport.ph

    ReplyDelete
  16. Hello nice tutorial. I want to change a database field while uploading data. how can i do that

    ReplyDelete