loading

How to Import CSV File Data into MySQL Database in CodeIgniter

How to Import CSV File Data into MySQL Database in CodeIgniter

How to Import CSV File Data into MySQL Database in CodeIgniter

0 Sales

Free

The Bulk Data Import capability is quite important for the web application's data management area. The import option allows you to input large data at once rather than one by one, which saves time when adding data into the database. To import data, the CSV (comma-separated values) file format is commonly used. The data is stored in plain text format in the CSV file, which can be readily loaded into the server.


The import capability makes it simple to input a large amount of data into the database with a single click from the website. You may use PHP's fgetcsv() function to parse and import a CSV file into a MySQL database. A custom library is required to import CSV data in CodeIgniter if your application is created with the CodeIgniter framework. Because CodeIgniter lacks a system library for importing CSV data. We will teach you how to import CSV file data into a MySQL database in CodeIgniter in this article.

Take a look at 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/

 

Create Database Table

A table in the database is required to hold the member's data. In the MySQL database, the following SQL creates a members table with some basic columns.

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 File Format

The CSV file will contain four fields based on the database table structure: Name, Email, Phone, and Status. The CSV file format will be similar to the following screen.

Config

autoload.php

Specify the widely utilized library and helper to run immediately 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 may import data from a CSV file into CodeIgniter using this CSVReader class.

 - parse csv() — This function parses a CSV file and returns the data as an array.
      - Using the PHP fopen() method, open the CSV file in read-only mode.
      - Using the PHP fgetcsv() method, extract info from the loaded CSV file.
      - Make an array out of the CSV data's fields and values.
      - Close the CSV file that has been opened.
      - Return CSV data in the form of an array.

<?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 (Members.php)

The CSV data import procedure is handled by the Members controller.

1. __construct() – Loads the necessary libraries (form validation), helpers (file), and models (member).
2. index() – Compile a list of the members' information.

      - SESSION status messages should be retrieved.
      - Using the getRows() function of the Member model, retrieve the rows from the database.
      - Pass the data from the members to the list view.

3. import() – Load data into the database from a CSV or Excel file.

      - The uploaded file is checked to ensure that it is a valid CSV file (using the Form Validation library).
      - If the document is uploaded, the CSVReader library is used to parse data from the CSV file.
      - The CSV data is inserted/updated in the database based on the email address.
      - The status message is saved in the SESSION variable.
      - The page has been redirected to a list view.

4. file_check() – This is a file upload validation callback function that verifies and validates the file input field's value and type (.csv).

      - The File helper function get mime by extension() 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 (Member.php)

The database-related tasks are handled by the Member model (Fetch, Insert, and Update).

1. __construct() – Give the table a name.
2. getRows() – Retrieve the members data from the database using the conditions indicated in $params. On success, it returns the records.
3. insert() – Enter member information into the database. If successful, returns the row ID; otherwise, returns FALSE.
4. update() – Update database member data based on the stated circumstance. If the function is successful, it returns TRUE; otherwise, 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

members/index.php

Eventually, all present members' data is retrieved from the database and displayed on the website.

 - At the top of the data list is an Import button.
 - When the Import button is pressed, a form with an input field for selecting a CSV file appears.
 - The specified file is sent to the Members controller's import() function.
 - If the CSV data is successfully imported into the database, the added member's data will be appended to the list.
 - (Optional) The HTML table and form are styled using the Bootstrap 4 library. 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 display or conceal 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>

Conclusion

In the sample script, we demonstrated how to import data from a CSV file into CodeIgniter. You may use CodeIgniter to import CSV or Excel file data into a MySQL database by using our proprietary CSVReader module. Furthermore, the functionality of this script may be simply modified to add other fields to the CSV file as needed.

LICENSE OF USE

You can use it for personal or commercial projects. You can't resell it partially or in this form.

PRODUCT INFO

Create Date : Feb 12, 2022

Updated Date : Feb 12, 2022

Ratings

Comments : 0

Downloads : 0