Import CSV file into database using CodeIgniter

CSV upload is useful when we want to upload bulk data in seconds, we insert our data via form one by one it takes a lot of time to insert data so in this case CSV upload saves our time. Some examples of CSV upload are leads upload, product upload, etc.


Step1:
Download CodeIgniter3

https://codeigniter.com/download


Step2:
Create a database named csvimport

CREATE DATABASE csvimport; 


Step3:
Create a leads table in csvimport database

CREATE TABLE leads (     
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,     
 name TEXT NOT NULL,     
 email TEXT NOT NULL,     
 mobile  TEXT NOT NULL); 


Step4:
Setup a connection in database.php file

$db['default'] = array( 
             'dsn'      => '', 
             'hostname' => 'localhost', 
             'username' => 'root', 
             'password' => '', 
             'database' => 'csvimport', 
             'dbdriver' => 'mysqli', 
             'dbprefix' => '', 
             'pconnect' => FALSE, 
             'db_debug' => (ENVIRONMENT !== 'production'), 
             'cache_on' => FALSE, 
             'cachedir' => '', 
             'char_set' => 'utf8', 
             'dbcollat' => 'utf8_general_ci', 
             'swap_pre' => '', 
             'encrypt' => FALSE, 
             'compress' => FALSE, 
             'stricton' => FALSE, 
             'failover' => array(), 
             'save_queries' => TRUE 
 ); 


Step5:
 create a file Csvimport.php in the libraries folder, write the following code:

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); 
 /** 
  * CodeIgniter CSV Import Class 
  * 
  * This library will help import a CSV file into 
  * an associative array. 
  * 
  * This library treats the first row of a CSV file 
  * as a column header row. 
  * 
  * 
  * @package         CodeIgniter 
  * @subpackage      Libraries 
  * @category        Libraries 
  * @author          Brad Stinson 
  */ 
 class Csvimport { 
     private $handle = ""; 
     private $filepath = FALSE; 
     private $column_headers = FALSE; 
     private $initial_line = 0; 
     private $delimiter = ","; 
     private $detect_line_endings = FALSE; 
    /** 
      * Function that parses a CSV file and returns results 
      * as an array. 
      * 
      * @access  public 
      * @param   filepath        string  Location of the CSV file 
      * @param   column_headers  array   Alternate values that will be used for array keys instead of first line of CSV 
      * @param   detect_line_endings  boolean  When true sets the php INI settings to allow script to detect line endings. Needed for CSV files created on Macs. 
      * @param   initial_line  integer  Sets the line of the file from which start parsing data. 
      * @param   delimiter  string  The values delimiter (e.g. ";" or ","). 
      * @return  array 
      */ 
     public function get_array($filepath=FALSE, $column_headers=FALSE, $detect_line_endings=FALSE, $initial_line=FALSE, $delimiter=FALSE) 
     { 
         // Raise memory limit (for big files) 
         ini_set('memory_limit', '20M'); 
         
         // File path 
         if(! $filepath) 
         { 
             $filepath = $this->_get_filepath();    
         } 
         else 
         {   
             // If filepath provided, set it 
             $this->_set_filepath($filepath); 
         } 
         // If file doesn't exists, return false 
         if(! file_exists($filepath)) 
         { 
             return FALSE;            
         } 
         // auto detect row endings 
         if(! $detect_line_endings) 
         { 
             $detect_line_endings = $this->_get_detect_line_endings();    
         } 
         else 
         {   
             // If detect_line_endings provided, set it 
             $this->_set_detect_line_endings($detect_line_endings); 
         } 
         // If true, auto detect row endings 
         if($detect_line_endings) 
         { 
             ini_set("auto_detect_line_endings", TRUE); 
         } 
         // Parse from this line on 
         if(! $initial_line) 
         { 
             $initial_line = $this->_get_initial_line();    
         } 
         else 
         { 
             $this->_set_initial_line($initial_line); 
         } 
         // Delimiter 
         if(! $delimiter) 
         { 
             $delimiter = $this->_get_delimiter();    
         } 
         else 
         {   
             // If delimiter provided, set it 
             $this->_set_delimiter($delimiter); 
         } 
         // Column headers 
         if(! $column_headers) 
         { 
             $column_headers = $this->_get_column_headers();    
         } 
         else 
         { 
             // If column headers provided, set them 
             $this->_set_column_headers($column_headers); 
         } 
         // Open the CSV for reading 
         $this->_get_handle(); 
         
         $row = 0; 
         while (($data = fgetcsv($this->handle, 0, $this->delimiter)) !== FALSE) 
         {     
             if ($data[0] != NULL) 
             { 
                 if($row < $this->initial_line) 
                 { 
                     $row++; 
                     continue; 
                 } 
                 // If first row, parse for column_headers 
                 if($row == $this->initial_line) 
                 { 
                     // If column_headers already provided, use them 
                     if($this->column_headers) 
                     { 
                         foreach ($this->column_headers as $key => $value) 
                         { 
                             $column_headers[$key] = trim($value); 
                         } 
                     } 
                     else // Parse first row for column_headers to use 
                     { 
                         foreach ($data as $key => $value) 
                         { 
                               $column_headers[$key] = trim($value); 
                         }                
                     }          
                 } 
                 else 
                 { 
                     $new_row = $row - $this->initial_line - 1; // needed so that the returned array starts at 0 instead of 1 
                     foreach($column_headers as $key => $value) // assumes there are as many columns as their are title columns 
                     { 
                     $result[$new_row][$value] = utf8_encode(trim($data[$key])); 
                     } 
                 } 
             
                 unset($data); 
             
                 $row++; 
             } 
         } 
   
         $this->_close_csv(); 
         return $result; 
     } 
     
     /** 
      * Sets the "detect_line_endings" flag 
      * 
      * @access  private 
      * @param   detect_line_endings    bool  The flag bit 
      * @return  void 
      */ 
     private function _set_detect_line_endings($detect_line_endings) 
     { 
         $this->detect_line_endings = $detect_line_endings; 
     } 
     /** 
      * Sets the "detect_line_endings" flag 
      * 
      * @access  public 
      * @param   detect_line_endings    bool  The flag bit 
      * @return  void 
      */ 
     public function detect_line_endings($detect_line_endings) 
     { 
         $this->_set_detect_line_endings($detect_line_endings); 
         return $this; 
     } 
     /** 
      * Gets the "detect_line_endings" flag 
      * 
      * @access  private 
      * @return  bool 
      */ 
     private function _get_detect_line_endings() 
     { 
         return $this->detect_line_endings; 
     } 
     /** 
      * Sets the initial line from which start to parse the file 
      * 
      * @access  private 
      * @param   initial_line    int  Start parse from this line 
      * @return  void 
      */ 
     private function _set_initial_line($initial_line) 
     { 
        return $this->initial_line = $initial_line; 
     } 
     /** 
      * Sets the initial line from which start to parse the file 
      * 
      * @access  public 
      * @param   initial_line    int  Start parse from this line 
      * @return  void 
      */ 
     public function initial_line($initial_line) 
     { 
         $this->_set_initial_line($initial_line); 
         return $this; 
     } 
     /** 
      * Gets the initial line from which start to parse the file 
      * 
      * @access  private 
      * @return  int 
      */ 
     private function _get_initial_line() 
     { 
         return $this->initial_line; 
     } 
     /** 
      * Sets the values delimiter 
      * 
      * @access  private 
      * @param   initial_line    string  The values delimiter (eg. "," or ";") 
      * @return  void 
      */ 
     private function _set_delimiter($delimiter) 
     { 
         $this->delimiter = $delimiter; 
     } 
     /** 
      * Sets the values delimiter 
      * 
      * @access  public 
      * @param   initial_line    string  The values delimiter (eg. "," or ";") 
      * @return  void 
      */ 
     public function delimiter($delimiter) 
     { 
         $this->_set_delimiter($delimiter); 
         return $this; 
     } 
     /** 
      * Gets the values delimiter 
      * 
      * @access  private 
      * @return  string 
      */ 
     private function _get_delimiter() 
     { 
         return $this->delimiter; 
     } 
     /** 
      * Sets the filepath of a given CSV file 
      * 
      * @access  private 
      * @param   filepath    string  Location of the CSV file 
      * @return  void 
      */ 
     private function _set_filepath($filepath) 
     { 
         $this->filepath = $filepath; 
     } 
     /** 
      * Sets the filepath of a given CSV file 
      * 
      * @access  public 
      * @param   filepath    string  Location of the CSV file 
      * @return  void 
      */ 
     public function filepath($filepath) 
     { 
         $this->_set_filepath($filepath); 
         return $this; 
     } 
     /** 
      * Gets the filepath of a given CSV file 
      * 
      * @access  private 
      * @return  string 
      */ 
     private function _get_filepath() 
     { 
         return $this->filepath; 
     } 
    /** 
      * Sets the alternate column headers that will be used when creating the array 
      * 
      * @access  private 
      * @param   column_headers  array   Alternate column_headers that will be used instead of first line of CSV 
      * @return  void 
      */ 
     private function _set_column_headers($column_headers='') 
     { 
         if(is_array($column_headers) && !empty($column_headers)) 
         { 
             $this->column_headers = $column_headers; 
         } 
     } 
     /** 
      * Sets the alternate column headers that will be used when creating the array 
      * 
      * @access  public 
      * @param   column_headers  array   Alternate column_headers that will be used instead of first line of CSV 
      * @return  void 
      */ 
     public function column_headers($column_headers) 
     { 
         $this->_set_column_headers($column_headers); 
         return $this; 
     } 
     /** 
      * Gets the alternate column headers that will be used when creating the array 
      * 
      * @access  private 
      * @return  mixed 
      */ 
     private function _get_column_headers() 
     { 
         return $this->column_headers; 
     } 
    /** 
      * Opens the CSV file for parsing 
      * 
      * @access  private 
      * @return  void 
      */ 
     private function _get_handle() 
     { 
         $this->handle = fopen($this->filepath, "r"); 
     } 
    /** 
      * Closes the CSV file when complete 
      * 
      * @access  private 
      * @return  array 
      */ 
     private function _close_csv() 
     { 
         fclose($this->handle); 
     }    
 } 
   


Step6:
In a default view welcome_message.php , write the following code:

<?php 
 defined('BASEPATH') OR exit('No direct script access allowed'); 
 ?><!DOCTYPE html> 
 <html lang="en"> 
 <head> 
             <meta charset="utf-8"> 
             <title>CSV Import</title> 
   
 </head> 
 <body> 
   
 <div id="container"> 
             <form method="post" enctype="multipart/form-data"> 
             <input type="file" name="csv_file"> 
             <input type="submit" name="submit"> 
             </form> 
 </div> 
   
 </body> 
 </html> 
   


Step7:
In a Controller Welcome.php, write the following code:

<?php 
 defined('BASEPATH') OR exit('No direct script access allowed'); 
   
 class Welcome extends CI_Controller { 
   
             /** 
              * Index Page for this controller. 
              * 
              * Maps to the following URL 
              *                      http://example.com/index.php/welcome 
              *          - or - 
              *                      http://example.com/index.php/welcome/index 
              *          - or - 
              * Since this controller is set as the default controller in 
              * config/routes.php, it's displayed at http://example.com/ 
              * 
              * So any other public methods not prefixed with an underscore will 
              * map to /index.php/welcome/<method_name> 
              * @see https://codeigniter.com/user_guide/general/urls.html 
              */ 
               
               
             
               
             public function index() 
             { 
                         
                         if(isset($_POST['submit'])) 
                         { 
                                     $file_data = $this->csvimport->get_array($_FILES["csv_file"]["tmp_name"]); 
             $i=1;     
             
             
             
             foreach($file_data as $row) 
                         { 
                                      $data['name']=$row['Name']; 
                                      $data['email']=$row['Email']; 
                                      $data['mobile']=$row['Mobile']; 
                                       
                                      $this->crud->insert('leads',$data); 
                         } 
                         
                         echo 'inserted successfully'; 
                         
                         } 
                         
                         $this->load->view('welcome_message'); 
             } 
 } 
   


Step8:
Create a file Crud.php in the models folder, write the following code:

<?php 
  class Crud extends CI_Model{ 
      
      
  function insert($table,$data) 
              { 
                          $result= $this->db->insert($table, $data); 
                          return $result; 
              } 
              
              
              
              function update($table,$id,$data) 
              { 
                          $this->db->where('id', $id); 
                          return $this->db->update($table,$data); 
              } 
    
    
              
              function get_data($table) 
              {   
                 $data= $this->db->get($table); 
                 return $data->result();                
              } 
    
              
              function delete($table,$id) 
              { 
                          $this->db->where('id', $id); 
                          return $this->db->delete($table); 
              } 
              
    
              public function fetchdatabyid($id,$table) 
      { 
                                      $this->db->where('id',$id); 
                  $data=$this->db->get($table); 
                  return $data->result(); 
      } 
    
              
    
              
  } 
   
  

Note:
  • First,Your file should be in .csv format not in .xlsx format.
  • Second, download CSV dummy file format from this link Download CSV File in this file you will see the column heading which you have to use in controller function, so change this heading according to your need and also change in controller function index where we have used column heading of CSV.
  • Try to write your column heading without space in CSV file.

Download Source Code

Categories: web development codeigniter

Trending Courses

CodeIgniter

Regular : 45 Days

Fastrack : 20 Days

Crash : 10 Days

Advance Digital Marketing

Regular : 6 Months

Fastrack : 3 Months

Crash : 2 Months

React JS

Regular : 45 Days

Fastrack : 20 Days

Crash : 7 Days

Laravel

Regular : 45 Days

Fastrack : 20 Days

Crash : 10 Days

Front End Developer

Regular : 7 Month

Fastrack : 4 Month

Crash : 2 Month

Related Blogs

Request For Demo