In this tutorial, you will learn how to import CSV file data into a MySQL database using CodeIgniter. CSV(Comma-separated values) file format is popular to insert the bulk amount of data into the database. CSV files are mostly used to import or to export data from the database. Using fgetcsv() function you can parse the CSV values and insert them into the Mysql database.  But if you are using the CodeIgniter framework you can use the csvimport library to parse and manipulate the CSV records. Below example demonstrate the CSV import functionality, In the example, we will use the- 
  • employees table
  • List all the existing employees on the webpage.
  • Download the sample CSV file
  • Insert the records into the CSV file
  • Import the CSV file
  • Refresh the employee listing.
Before going to start the CSV import example, take a look of the application structure.

employees_application
 |
 |____ applications/
 |     |
 |     |______controllers/
 |     |       |
 |     |       |_______Employees_Controller.php
 |     |
 |     |______libraries/
 |     |      |
 |     |      |_______Csvimport.php
 |     |
 |     |_______models/
 |     |       |
 |     |       |_______Employees_model.php
 |     |
 |     |______viwes/
 |            |
 |            |_______display_employee.php

Click on the Live Demo button to see the output.
We have used the employees table in this demo, below is the structure of the employees table in the MySQL database.

Create Table in MySql (Phpmyadmin)

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `email` varchar(80) NOT NULL,
  `username` varchar(45) DEFAULT NULL,
  `mobile` varchar(50) DEFAULT NULL,
  `bio` text,
  `profile_pic` text,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`name`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now create the employees controller so that we can write the logic to list the employees records on the webpage and import records into the mysql database using CSV file. Below is the Employees_Controller.php

Employees_Controller.php (application/controllers/Employees_Controller.php)

<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class Employees_Controller extends CI_Controller {

    public $page_data;

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

    public function listing() {
        $this->page_data['employee_details'] = $this->employees_model->get();
        $this->load->view('display_employee', $this->page_data);
    }

    public function import_csv() {
        $this->load->library('Csvimport');
        //Check file is uploaded in tmp folder
        if (is_uploaded_file($_FILES['file']['tmp_name'])) {
            //validate whether uploaded file is a csv file
            $csvMimes = 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');
            $mime = get_mime_by_extension($_FILES['file']['name']);
            $fileArr = explode('.', $_FILES['file']['name']);
            $ext = end($fileArr);
            if (($ext == 'csv') && in_array($mime, $csvMimes)) {
                $file = $_FILES['file']['tmp_name'];
                $csvData = $this->csvimport->get_array($file);
                $headerArr = array("Name", "Phone", "Email", "Created at");
                if (!empty($csvData)) {
                    //Validate CSV headers
                    $csvHeaders = array_keys($csvData[0]);
                    $headerMatched = 1;
                    foreach ($headerArr as $header) {
                        if (!in_array(trim($header), $csvHeaders)) {
                            $headerMatched = 0;
                        }
                    }
                    if ($headerMatched == 0) {
                        $this->session->set_flashdata("error_msg", "CSV headers are not matched.");
                        redirect('Employees_Controller/listing');
                    } else {
                        foreach ($csvData as $row) {
                            $employee_data = array(
                                "name" => $row['Name'],
                                "mobile" => $row['Phone'],
                                "email" => $row['Email'],
                                "created_at" => date('Y-m-d H:i:s'),
                                "updated_at" => date('Y-m-d H:i:s'));
                            $table_name = "demo_users";
                            $this->employees_model->save($table_name, $employee_data);
                        }
                        $this->session->set_flashdata("success_msg", "CSV File imported successfully.");
                        redirect('Employees_Controller/listing');
                    }
                }
            } else {
                $this->session->set_flashdata("error_msg", "Please select CSV file only.");
                redirect('Employees_Controller/listing');
            }
        } else {
            $this->session->set_flashdata("error_msg", "Please select a CSV file to upload.");
            redirect('Employees_Controller/listing');
        }
    }

}

In Employees_Controller we have two functions, listing() function is used to list all the employees on the webpage and import_csv() function is used to import the records from CSV

Now create the Employees_model to get all the employees data from the employees table.

Employees_model.php (application/models/Employees_model.php)

<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class Employees_model extends CI_Model {

    public function get() {
        $sql = "SELECT * FROM employees";
        $q = $this->db->query($sql);
        if ($q->num_rows() > 0) {
            foreach ($q->result() as $row) {
                $data[] = $row;
            }
            return $data;
        }
    }

    public function save($table_name, $data = array()) {
        $insert = $this->db->insert($table_name, $data);
        return $insert;
    }

}
Now create the display_employee View to list all the employees on the webpage.

display_employee.php (application/views/display_employee.php)

<!DOCTYPE html>
<html lang="en">
    <head>
        <title>Live Demo: Import csv file Codeigniter</title>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
        <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
    </head>
    <body>
        <div class="container" style="max-width:992px;margin:0 auto;margin-top:50px;">  
            <div>
                <h1 style="margin-bottom:50px;font-size:28px;">Live Demo: Import csv file Codeigniter</h1>              
                <div class="panel panel-default">
                    <div class="panel-heading">
                        Users List
                        <a href="javascript:void(0);" onclick="$('#import_form').slideToggle();">Import Users</a>
                        <div style="color:red;">
                            <?php
                            $error_msg = "";
                            if ($this->session->flashdata('error_msg') && $this->session->flashdata('error_msg') != "") {
                                $error_msg = $this->session->flashdata('error_msg');
                            }
                            echo $error_msg;
                            ?>
                        </div>
                        <div style="color:green;">
                            <?php
                            $success_msg = "";
                            if ($this->session->flashdata('success_msg') && $this->session->flashdata('success_msg') != "") {
                                $success_msg = $this->session->flashdata('success_msg');
                            }
                            echo $success_msg;
                            ?>
                        </div>
                    </div>
                    <div class="panel-body">
                        <form action="<?php echo site_url("Employees_Controller/import_csv"); ?>" method="post" enctype="multipart/form-data" id="import_form">
                            <div style="margin-bottom:40px;">
                                <input type="file" name="file" style="display:inline-block;"/>
                                <input type="submit" class="btn btn-primary" name="importBtn" value="IMPORT">
                            </div>
                        </form>
                        <table class="table table-bordered">
                            <thead>
                                <tr>
                                    <th>id</th>
                                    <th>Name</th>
                                    <th>Email</th>
                                    <th>Phone</th>
                                    <th>Created at</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                if (isset($employee_details) && !empty($employee_details)) {
                                    foreach ($employee_details as $employee) {
                                        ?>
                                        <tr>
                                            <td><?php echo $employee->id; ?></td>
                                            <td><?php echo $employee->name; ?></td>
                                            <td><?php echo $employee->email; ?></td>
                                            <td><?php echo $employee->mobile; ?></td>
                                            <td><?php echo $employee->created_at; ?></td>                                    
                                        </tr>
                                        <?php
                                    }
                                } else {
                                    ?>
                                    <tr><td colspan="5">No employees(s) found.....</td></tr>
                                <?php } ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </body>
</html>


Now create the Csvimport library file to manipulate with the CSV file, We have loaded the Csvimport library inside the import_csv() function like this
$this->load->library('Csvimport');

Csvimport.php (application/libraries/Csvimport.php)

<?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);
    }

}

Click on Live Demo button to see the output, Download the Sample CSV file and insert few records in it and try to upload the csv file.

Written by
Check out this Author's contributed articles.
If you like Tutorialsmint and would like to contribute, you can also write an article using Write an Article. See your article appearing on the Tutorialsmint main page and help other programmers to enhance their knowledge.

Comments(0)

RECENT TUTORIALS
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Edit Post
Add Reply