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.
