How to Import CSV into MySQL Database with CodeIgniter

You may have some situations when you were asked to input some data directly to the database or with the User Interface. Well, that would have been just fine if you weren't asked to input over a hundred rows of data.
In this tutorial we're going to see how to import data from a Comma Delimited Values (csv) formated file to MySQL Database using CodeIgniter. Basically, we will be populating our database table with an excel file. In this tutorial, we will be importing list of students into our database tbl_users table.
At the end of this tutorial you'll have a User Interface like the following.

Step 1. Create a Database Table

Create a table named "tbl_students" with the following sql query.

CREATE TABLE `tbl_students` (
 `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
 `first_name` VARCHAR(50) NOT NULL,
 `last_name` VARCHAR(50) NOT NULL,
 `email` VARCHAR(50) NOT NULL,
 `gender` VARCHAR(10) NOT NULL,
 `address` TINYTEXT NOT NULL,
 PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

You would have a table like the following image after execution of the above sql command.

As you would probably know by now, we'll be saving user's first name, last name, email, gender and address. The data within an excel column will be saved to a database table field, Basically, we'll be having an excel file with five columns, we don't need a column for id since it's auto-incremented in our database table.
Before we proceed to coding, we need to save our excel file into (.csv) format. To do that

Step 2. Create Model

First we need to create a model so we could execute queries to our database. Go ahead and create a file name "Students_tbl_model" under "applications/models/" and paste the following code.

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Students_tbl_model extends CI_Model {

    public $variable;

    public function __construct()
    {
        parent::__construct();
    }
    public function add($row, $dateCreated = FALSE) {
        $this->db->insert('tbl_students', $row);
        return $this->db->insert_id();
    }
    public function getAll() {
        if (isset($this->orderby) && $this->orderby != '') {
            $this->db->order_by($this->orderby);
        }
        $query = $this->db->get('tbl_students');
        return $query->result_array();
    }
}

/* End of file  */
/* Location: ./application/models/ */

We only have add function which we will be call in our controller when we need to add some data to our "tbl_students" database table.

Step 3. Create a Controller

Go ahead and create a file name "Students.php" in "applications/controllers/" and paste the following code.

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Students extends CI_Controller {

    public function __construct()
    {
        parent::__construct();
        $this->load->model('students_tbl_model');
    }

    public function index()
    {
        $data['students'] = $this->students_tbl_model->getAll();
        $this->load->view('student_details',$data);
    }
}

/* End of file  */
/* Location: ./application/controllers/ */

What the controller do when called is load a view named "students_details".

Step 4. Create a View

We will be displaying a table of all students and a modal form which we'll be using to import csv file to our database.
We'll also be using Ajax and FormData to submit our form. FormData provides a way to easily construct a set of key/value pairs representing form fields and their values, which can then easily be sent using XMLHttpRequest.send(), basically, our Ajax request.
When using FormData, it's imperative that you set the contentType option to false, forcing JQuery not to add a Content-Type header for you, otherwise, the boundary string will be missing from it. In addition, you must set the processData flag to false, otherwise, JQuery will try to convert your FormData into a string, which will fail. Alright, go ahead and create a file name "student_details.php" under "applications/view/" and paste the following code.

<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CodeIgniter Contact Form Example</title>
    <!--load bootstrap css-->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-alpha.5/css/bootstrap.min.css" integrity="sha384-AysaV+vQoT3kOAXZkl02PThvDr8HYKPZhNT5h/CXfBThSRXQ6jW5DO2ekP5ViFdi" crossorigin="anonymous">

    <style>
        .container{
            padding:5%;
        }
        .modal .modal-body{
            display: flex;
            align-items: center;
            justify-content: center;
            margin: 57px 0px 57px 0px;
        }
        .modal .modal-title{
            text-align:center;
        }
        .custom-file-control{
            position: absolute;
            top: 0;
            right: 0;
            left: 0;
            z-index: 5;
            height: 2.5rem;
            padding: .5rem 1rem;
            line-height: 1.5;
            color: #555;
            -webkit-user-select: none;
            -moz-user-select: none;
            -ms-user-select: none;
            user-select: none;
            background-color: #fff;
            border: 1px solid #ddd;
            border-radius: .25rem;
        }
        .custom-file-control::before{
            content:"Browse";
            position: absolute;
            top: -1px;
            right: -1px;
            bottom: -1px;
            z-index: 6;
            display: block;
            height: 2.5rem;
            padding: .5rem 1rem;
            line-height: 1.5;
            color: #555;
            background-color: #eee;
            border: 1px solid #ddd;
            border-radius: 0 .25rem .25rem 0;
        }
        .custom-file-control::after{
            content:attr(data-attr);
        }
    </style>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
    <script>var base_url = "<?=base_url();?>";</script>
</head>
<body>
    <div class="container">
        <div class="row">
            <div class="col-sm-12">
                <!-- Button trigger modal -->
                <button type="button" class="btn btn-primary btn-lg" data-toggle="modal" data-target="#myModal">
                    Import
                </button>
            </div>
        </div>
        <div class="row">
            <div class="col-xs-12">
                <table class="table table-striped col-sm-12">
                    <thead>
                        <tr>
                            <td>First Name</td>
                            <td>Last Name</td>
                            <td>Email</td>
                            <td>Gender</td>
                            <td>Address</td>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach((array)$students as $student): ?>
                            <tr>
                                <td><?= $student['first_name']?></td>
                                <td><?= $student['last_name']?></td>
                                <td><?= $student['email']?></td>
                                <td><?= $student['gender']?></td>
                                <td><?= $student['address']?></td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
    <!-- Modal -->
    <div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
        <div class="modal-dialog" role="document">
            <div class="modal-content">
                <form action="#" id="import-form">
                    <div class="modal-header">
                        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                            <span aria-hidden="true">&times;</span>
                        </button>
                        <h4 class="modal-title text-center" id="myModalLabel">Import CSV File to DB</h4>
                    </div>
                    <div class="modal-body">
                        <div class="form-group">
                            <label class="custom-file col-xs-12">
                                <input type="file" id="file" class="custom-file-input" name="file" placeholder="Choose file">
                                <span class="custom-file-control" data-attr="Choose file..."></span>
                            </label>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-dismiss="modal">Cancel</button>
                        <button type="submit" class="btn btn-outline-success float-xs-right" data-loading="processing...">Import to DB</button>
                    </div>
                </form>
            </div>
        </div>
    </div>
</div>
    <script src="https://www.atlasestateagents.co.uk/javascript/tether.min.js"></script><!-- Tether for Bootstrap -->
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-alpha.5/js/bootstrap.min.js" integrity="sha384-BLiI7JTZm+JWlgKa0M0kGRpJbF2J8q+qreVrKBC47e3K6BW78kGLrCkeRX6I9RoK" crossorigin="anonymous"></script>
    <script>
        $(function(){
            $('input[name="file"]').on('change',function(e){
                var filename = document.getElementById("file").files[0].name;
                $(this).next().attr('data-attr',filename);
            })
            $('#import-form').on('submit',function(e){
                e.preventDefault();
                var $btn = $(this).find('button[type="submit"]');
                var formdata = new FormData(this);
                $.ajax({
                    url: base_url+'students/import',
                    type: 'POST',
                    dataType: 'JSON',
                    data:formdata,
                    cache:false,
                    contentType: false,
                    processData: false,
                    beforeSend:function(){
                        $btn.button('loading');
                    },
                    success:function(response){
                        $('.form-group.has-error').removeClass('has-error').find('span.text-danger').remove();
                        switch(response.status){
                            case 'form-incomplete':
                                $.each(response.errors, function(key,val){
                                    if(val.error!=''){
                                        $(val.field).closest('.form-group').addClass('has-error').append(val.error);
                                    }
                                })
                            break;
                            case 'success':
                                window.location.reload(true);
                            break;
                            case 'error':
                                console.log(response.message);
                            break;
                        }
                    },
                    error: function(jqXHR,textStatus,error){
                        console.log('Unable to send request!');
                    }
                }).always(function(){
                    $btn.button('reset');
                });
            })
        })
    </script>
</body>
</html>

Before sending our form inputs to our controller, we are disabling our submit button to prevent the user from resubmitting the form until a server response for the previous request is thrown back.
We then add a function named import in our controller Student as specified on our Ajax url option. Copy the following code to your Students controller.

public function import()
    {
        if(!$this->input->is_ajax_request()){
            show_404();
        }
        $this->form_validation->set_error_delimiters('<span class="text-danger">', '</span>');
        $this->form_validation->set_rules('file','File','callback_notEmpty');
        
        $response= false;
        if(!$this->form_validation->run()){
            $response['status']    = 'form-incomplete';
            $response['errors']    =    array(
                array(
                    'field'    => 'input[name="file"]',
                    'error'    => form_error('file')
                )
            );
        }
        else{
            try{
                
                $filename = $_FILES["file"]["tmp_name"];
                if($_FILES['file']['size'] > 0)
                {
                    $file = fopen($filename,"r");
                    $is_header_removed = FALSE;
                    while(($importdata = fgetcsv($file, 10000, ",")) !== FALSE)
                    {
                        if(!$is_header_removed){
                            $is_header_removed = TRUE;
                            continue;
                        }
                        $row = array(
                            'first_name'    =>  !empty($importdata[0])?$importdata[0]:'',
                            'last_name'     =>  !empty($importdata[1])?$importdata[1]:'',
                            'email'         =>  !empty($importdata[2])?$importdata[2]:'',
                            'gender'        =>  !empty($importdata[3])?$importdata[3]:'',
                            'address'       =>  !empty($importdata[4])?$importdata[4]:''
                        );
                        $this->db->trans_begin();
                        $this->students_tbl_model->add($row);
                        if(!$this->db->trans_status()){
                            $this->db->trans_rollback();
                            $response['status']='error';
                            $response['message']='Something went wrong while saving your data';
                            break;
                        }else{
                            $this->db->trans_commit();
                            $response['status']='success';
                            $response['message']='Successfully added new record.';
                        }
                    }
                    fclose($file);
                }
               
            }
            catch(Exception $e){
                $this->db->trans_rollback();
                $response['status']='error';
                $response['message']='Something went wrong while trying to communicate with the server.';
            }
        }
        echo json_encode($response);
    }
    public function notEmpty(){
        if(!empty($_FILES['file']['name'])){
            return TRUE;
        }
        else{
            $this->form_validation->set_message('notEmpty','The {field} field can not be empty.');
            return FALSE;
        }
    }

Import function first check whether the request is ajax, otherwise, redirect to 404 page. We then validate the form input using our customized rule "notEmpty". The callback function "notEmpty" returns true when there is a file submitted, otherwise, throws false. It then open the file when it passes the rules. Afterwards, iterated over the file rows using a while loop discarding the first row since we don't need column labels, we're only concerned about the student info.
The csv columns is then saved into an array variable named "$row" which is then passed to our "students_tbl_model" to be inserted on our database table.
If all database transaction run smoothly, a success status and a message is thrown back to our Ajax success callback where in we either reload the page or print errors depending on the response status. The form submit button is then re enabled.