The Bulk Data Import tool in the web application’s data management section is really beneficial. The import option allows you to insert large amounts of data at once rather than one by one, which saves time in the database. Importing data is often done using the CSV (comma-separated values) file format. The data is stored in plain text format in the CSV file, which can be readily loaded into the server.
The website’s import functionality makes it simple to import a large amount of data into the database with a single click. You may parse and import a CSV file into a MySQL database with PHP using the fgetcsv() method. If you’re using the CodeIgniter framework, you’ll need a custom library to import CSV data into CodeIgniter. Because there is no system library for importing CSV data in CodeIgniter. In this article, we’ll teach you how to use CodeIgniter to import data from a CSV file into a MySQL database.
Using the CodeIgniter CSVReader library, we will import members data from a CSV file into the database in the example code. The following procedure will be used to showcase CodeIgniter’s CSV Import feature.
Get all of the members’ information from the database and display it on a web page.
Upload a CSV file using this form.
Data from CSV files is parsed and imported into the database.
Examine the file structure before beginning to implement the import CSV file to the database in the CodeIgniter 3.x application.
codeigniter_csv_import/ ├── application/ │ ├── controllers/ │ │ └── Members.php │ ├── libraries/ │ │ └── CSVReader.php │ ├── models/ │ │ └── Member.php │ └── views/ │ └── members/ │ └── index.php └── assets/ ├── css/ ├── images/ └── bootstrap/
Make a database table.
A table in the database is required to store the data of the members. In the MySQL database, the following SQL creates a members table with some basic fields. structure.
CREATE TABLE `members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`status` enum('Active','Inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CSV (Comma Separated Values)
The CSV file will include four fields based on the database table structure: Name, Email, Phone, and Status. The CSV file will be in the same format as the screen below.
Config \sautoload.php
Define the widely used library and helper to load automatically on every request in the config/autoload.php file.
$autoload['libraries'] = array('database', 'session');
$autoload['helper'] = array('url');
Libraries
CSVReader.php
In a CodeIgniter 3.x application, the CSVReader library is used to read a CSV file and transform CSV data into an array. You can import data from a CSV file into CodeIgniter using this CSVReader class.
parse csv() — Returns an array of data from a CSV file.
Using the PHP fopen() function, open the CSV file in read-only mode.
Using the PHP fgetcsv() method, parse data from the opened CSV file.
Create an array with the CSV data’s fields and values.
Close the CSV file that has been opened.
Return data from a CSV file in an array format.
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
/**
* CSV Reader for CodeIgniter 3.x
*
* Library to read the CSV file. It helps to import a CSV file
* and convert CSV data into an associative array.
*
* This library treats the first row of a CSV file
* as a column header row.
*
*
* @package CodeIgniter
* @category Libraries
* @author CodexWorld
* @license http://www.codexworld.com/license/
* @link http://www.codexworld.com
* @version 3.0
*/
class CSVReader {
// Columns names after parsing
private $fields;
// Separator used to explode each line
private $separator = ';';
// Enclosure used to decorate each field
private $enclosure = '"';
// Maximum row size to be used for decoding
private $max_row_size = 4096;
/**
* Parse a CSV file and returns as an array.
*
* @access public
* @param filepath string Location of the CSV file
*
* @return mixed|boolean
*/
function parse_csv($filepath){
// If file doesn't exist, return false
if(!file_exists($filepath)){
return FALSE;
}
// Open uploaded CSV file with read-only mode
$csvFile = fopen($filepath, 'r');
// Get Fields and values
$this->fields = fgetcsv($csvFile, $this->max_row_size, $this->separator, $this->enclosure);
$keys_values = explode(',', $this->fields[0]);
$keys = $this->escape_string($keys_values);
// Store CSV data in an array
$csvData = array();
$i = 1;
while(($row = fgetcsv($csvFile, $this->max_row_size, $this->separator, $this->enclosure)) !== FALSE){
// Skip empty lines
if($row != NULL){
$values = explode(',', $row[0]);
if(count($keys) == count($values)){
$arr = array();
$new_values = array();
$new_values = $this->escape_string($values);
for($j = 0; $j < count($keys); $j++){
if($keys[$j] != ""){
$arr[$keys[$j]] = $new_values[$j];
}
}
$csvData[$i] = $arr;
$i++;
}
}
}
// Close opened CSV file
fclose($csvFile);
return $csvData;
}
function escape_string($data){
$result = array();
foreach($data as $row){
$result[] = str_replace('"', '', $row);
}
return $result;
}
}Controllers are people who control things (Members.php)
The CSV data import is handled by the Members controller.
__construct() – Loads the form validation library, helper (file), and model (member).
index() – Returns a list of the members’ data.
SESSION status messages can be retrieved.
The getRows() method of the Member model is used to retrieve records from the database.
The data from the members should be passed to the list view.
Import data from a CSV or Excel file into the database with import().
Validation is performed on the submitted file to ensure that it is a valid CSV file (using the Form Validation library).
Data is parsed from the CSV file using the CSVReader library if the file is uploaded.
The CSV data is inserted/updated in the database based on the email address.
The SESSION stores the status message.
The page is now displayed in a list format.
file check() – This is a file upload validation callback function that checks and validates the value and type of the file input field (.csv).
The File helper’s get mime by extension() function is used to determine the MIME type of the specified file.
<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Members extends CI_Controller {
function __construct() {
parent::__construct();
// Load member model
$this->load->model('member');
// Load form validation library
$this->load->library('form_validation');
// Load file helper
$this->load->helper('file');
}
public function index(){
$data = array();
// Get messages from the session
if($this->session->userdata('success_msg')){
$data['success_msg'] = $this->session->userdata('success_msg');
$this->session->unset_userdata('success_msg');
}
if($this->session->userdata('error_msg')){
$data['error_msg'] = $this->session->userdata('error_msg');
$this->session->unset_userdata('error_msg');
}
// Get rows
$data['members'] = $this->member->getRows();
// Load the list page view
$this->load->view('members/index', $data);
}
public function import(){
$data = array();
$memData = array();
// If import request is submitted
if($this->input->post('importSubmit')){
// Form field validation rules
$this->form_validation->set_rules('file', 'CSV file', 'callback_file_check');
// Validate submitted form data
if($this->form_validation->run() == true){
$insertCount = $updateCount = $rowCount = $notAddCount = 0;
// If file uploaded
if(is_uploaded_file($_FILES['file']['tmp_name'])){
// Load CSV reader library
$this->load->library('CSVReader');
// Parse data from CSV file
$csvData = $this->csvreader->parse_csv($_FILES['file']['tmp_name']);
// Insert/update CSV data into database
if(!empty($csvData)){
foreach($csvData as $row){ $rowCount++;
// Prepare data for DB insertion
$memData = array(
'name' => $row['Name'],
'email' => $row['Email'],
'phone' => $row['Phone'],
'status' => $row['Status'],
);
// Check whether email already exists in the database
$con = array(
'where' => array(
'email' => $row['Email']
),
'returnType' => 'count'
);
$prevCount = $this->member->getRows($con);
if($prevCount > 0){
// Update member data
$condition = array('email' => $row['Email']);
$update = $this->member->update($memData, $condition);
if($update){
$updateCount++;
}
}else{
// Insert member data
$insert = $this->member->insert($memData);
if($insert){
$insertCount++;
}
}
}
// Status message with imported data count
$notAddCount = ($rowCount - ($insertCount + $updateCount));
$successMsg = 'Members imported successfully. Total Rows ('.$rowCount.') | Inserted ('.$insertCount.') | Updated ('.$updateCount.') | Not Inserted ('.$notAddCount.')';
$this->session->set_userdata('success_msg', $successMsg);
}
}else{
$this->session->set_userdata('error_msg', 'Error on file upload, please try again.');
}
}else{
$this->session->set_userdata('error_msg', 'Invalid file, please select only CSV file.');
}
}
redirect('members');
}
/*
* Callback function to check file value and type during validation
*/
public function file_check($str){
$allowed_mime_types = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain');
if(isset($_FILES['file']['name']) && $_FILES['file']['name'] != ""){
$mime = get_mime_by_extension($_FILES['file']['name']);
$fileAr = explode('.', $_FILES['file']['name']);
$ext = end($fileAr);
if(($ext == 'csv') && in_array($mime, $allowed_mime_types)){
return true;
}else{
$this->form_validation->set_message('file_check', 'Please select only CSV file to upload.');
return false;
}
}else{
$this->form_validation->set_message('file_check', 'Please select a CSV file to upload.');
return false;
}
}
}Models are a type of model that is used (Member.php
The Member model is in charge of database-related tasks (Fetch, Insert, and Update).
Define the table name with __construct().
getRows() – Retrieves the members’ data from the database depending on the $params requirements. On success, the records are returned.
insert() — Inserts data from a member into the database. If successful, returns the row ID; if unsuccessful, returns FALSE.
update() – Based on the supplied condition, update member data in the database. If the function succeeds, it returns TRUE; if it fails, it returns FALSE.
The Member model is in charge of database-related tasks (Fetch, Insert, and Update).
Define the table name with __construct().
getRows() – Retrieves the members’ data from the database depending on the $params requirements. On success, the records are returned.
insert() — Inserts data from a member into the database. If successful, returns the row ID; if unsuccessful, returns FALSE.
update() – Based on the supplied condition, update member data in the database. If the function succeeds, it returns TRUE; if it fails, it returns FALSE.
<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Member extends CI_Model{
function __construct() {
// Set table name
$this->table = 'members';
}
/*
* Fetch members data from the database
* @param array filter data based on the passed parameters
*/
function getRows($params = array()){
$this->db->select('*');
$this->db->from($this->table);
if(array_key_exists("where", $params)){
foreach($params['where'] as $key => $val){
$this->db->where($key, $val);
}
}
if(array_key_exists("returnType",$params) && $params['returnType'] == 'count'){
$result = $this->db->count_all_results();
}else{
if(array_key_exists("id", $params)){
$this->db->where('id', $params['id']);
$query = $this->db->get();
$result = $query->row_array();
}else{
$this->db->order_by('id', 'desc');
if(array_key_exists("start",$params) && array_key_exists("limit",$params)){
$this->db->limit($params['limit'],$params['start']);
}elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)){
$this->db->limit($params['limit']);
}
$query = $this->db->get();
$result = ($query->num_rows() > 0)?$query->result_array():FALSE;
}
}
// Return fetched data
return $result;
}
/*
* Insert members data into the database
* @param $data data to be insert based on the passed parameters
*/
public function insert($data = array()) {
if(!empty($data)){
// Add created and modified date if not included
if(!array_key_exists("created", $data)){
$data['created'] = date("Y-m-d H:i:s");
}
if(!array_key_exists("modified", $data)){
$data['modified'] = date("Y-m-d H:i:s");
}
// Insert member data
$insert = $this->db->insert($this->table, $data);
// Return the status
return $insert?$this->db->insert_id():false;
}
return false;
}
/*
* Update member data into the database
* @param $data array to be update based on the passed parameters
* @param $condition array filter data
*/
public function update($data, $condition = array()) {
if(!empty($data)){
// Add modified date if not included
if(!array_key_exists("modified", $data)){
$data['modified'] = date("Y-m-d H:i:s");
}
// Update member data
$update = $this->db->update($this->table, $data, $condition);
// Return the status
return $update?true:false;
}
return false;
}
}Views /index.php
Initially, the database is queried for all existing members’ information, which is then displayed on the webpage.
At the top of the data list is an Import button.
When you click the Import button, a form appears with an input area for selecting a CSV file.
The specified file is sent to the Members controller’s import() function.
If the CSV data is successfully imported into the database, the newly inserted member’s information will be added to the list.
(Optional) The HTML table and form are styled with the Bootstrap 4 library. As a result, include the Bootstrap library’s CSS file as well as the custom stylesheet file (if any).
formToggle() – This is a JavaScript function that allows you to show or hide the file upload form when you click the Import button.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>CodeIgniter CSV Import</title>
<!-- Bootstrap library -->
<link rel="stylesheet" href="<?php echo base_url('assets/bootstrap/bootstrap.min.css'); ?>">
<!-- Stylesheet file -->
<link rel="stylesheet" href="<?php echo base_url('assets/css/style.css'); ?>">
</head>
<body>
<div class="container">
<h2>Members List</h2>
<!-- Display status message -->
<?php if(!empty($success_msg)){ ?>
<div class="col-xs-12">
<div class="alert alert-success"><?php echo $success_msg; ?></div>
</div>
<?php if(!empty($error_msg)){ ?>
<div class="col-xs-12">
<div class="alert alert-danger"><?php echo $error_msg; ?></div>
</div>
<?php } ?>
<div class="row">
<!-- Import link -->
<div class="col-md-12 head">
<div class="float-right">
<a href="javascript:void(0);" class="btn btn-success" onclick="formToggle('importFrm');"><i class="plus"></i> Import</a>
</div>
</div>
<!-- File upload form -->
<div class="col-md-12" id="importFrm" style="display: none;">
<form action="<?php echo base_url('members/import'); ?>" method="post" enctype="multipart/form-data">
<input type="file" name="file" />
<input type="submit" class="btn btn-primary" name="importSubmit" value="IMPORT">
</form>
</div>
<!-- Data list table -->
<table class="table table-striped table-bordered">
<thead class="thead-dark">
<tr>
<th>#ID</th>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
<th>Status</th>
</tr>
</thead>
<tbody>
<?php if(!empty($members)){ foreach($members as $row){ ?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['email']; ?></td>
<td><?php echo $row['phone']; ?></td>
<td><?php echo $row['status']; ?></td>
</tr>
<?php } }else{ ?>
<tr><td colspan="5">No member(s) found...</td></tr>
<?php } ?>
</tbody>
</table>
</div>
</div>
<script>
function formToggle(ID){
var element = document.getElementById(ID);
if(element.style.display === "none"){
element.style.display = "block";
}else{
element.style.display = "none";
}
}
</script>
</body>
</html>Note :
We demonstrated how to import data from a CSV file into CodeIgniter in the sample script. You may use CodeIgniter to import CSV or Excel file data into a MySQL database using our proprietary CSVReader library. This script’s capabilities may also be simply modified to add extra fields to the CSV file as needed.