Import Data From Excel & CSV to mysql Using Codeigniter

torque-programming 0 Comments 6 min read

In this CodeIgniter Excel and CSV import tutorial, we will show you how to import data as Excel or CSV file formats. Excel and CSV are the best techniques for importing data in a file, and you can easily import data to Excel or CSV using the CodeIgniter Excel library.

Codeigniter Import Excel,CSV File

Contents

Download Codeigniter Latest
Basic Configurations
Download phpExcel Library
Create Library
Create Database With Table
Setup Database Credentials
Make New Controller
Create model
Create Views
Start Development server
Conclusion

Download Codeigniter Latest

In this next step, we’re going to download the newest version of Codeigniter. To do this, go to the website Download Codeigniter and get the most up-to-date setup. After you’ve downloaded it, unzip the setup in your local system’s xampp/htdocs/ folder. You can also rename your project folder to whatever you’d like.

Basic Configurations

Now it’s time to do some basic configuration settings on the config.php file. To do this, we’ll navigate to the application/config/config.php file and open it up in a text editor.

set base url like this:-

$config['base_url'] = 'http://localhost/projects/demo';

Download phpExcel Library

Download this excel library here : click here

Next, we need to download phpExcel library from this link, and extract into application/third_party folder. After extract this library move to PHPExcel folder like application/third_party/PHPExcel and also move PHPExcel.php file to application/third_party/PHPExcel.php.

Create Library

Now we need to create Excel.php file into application/library, So go to application/library and create one file name Excel.php and put the below code here.

<?php 
if (!defined('BASEPATH')) exit('No direct script access allowed');  
  
require_once APPPATH."/third_party/PHPExcel.php";
  
class Excel extends PHPExcel {
    public function __construct() {
        parent::__construct();
    }
}

Create Database With Table

Next, we need to create a database called “demo”. To create the database, open PHPMyAdmin and create a new database with the name “demo”. Once you have created the database, you can use the following SQL query to create a table in your database.

CREATE TABLE import (
    id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
    first_name varchar(100) NOT NULL COMMENT 'First Name',
    last_name varchar(100) NOT NULL COMMENT 'Last Name',
    email varchar(255) NOT NULL COMMENT 'Email Address',
    contact_no varchar(50) NOT NULL COMMENT 'Contact No',
    PRIMARY KEY (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=1;

Setup Database Credentials

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'demo',
    '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
);

Create Controller

Now we need to create a controller name Import.php. In this controller we will create some method/function. We will build some of the methods like :

Index() – This is used to showing users list
importFile() – This function is used to import excel or csv sheet

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Import extends CI_Controller
{
    // construct
    public function __construct()
    {
        parent::__construct();
        // load model
        $this->load->model('Import_model', 'import');
        $this->load->helper(array(
            'url',
            'html',
            'form'
        ));
    }
    public function index()
    {
        $this->load->view('import');
    }
    public function importFile()
    {
        if ($this->input->post('submit')) {
            $path = 'uploads/';
            require_once APPPATH . "/third_party/PHPExcel.php";
            $config['upload_path']   = $path;
            $config['allowed_types'] = 'xlsx|xls|csv';
            $config['remove_spaces'] = TRUE;
            $this->load->library('upload', $config);
            $this->upload->initialize($config);
            if (!$this->upload->do_upload('uploadFile')) {
                $error = array(
                    'error' => $this->upload->display_errors()
                );
            } else {
                $data = array(
                    'upload_data' => $this->upload->data()
                );
            }
            if (empty($error)) {
                if (!empty($data['upload_data']['file_name'])) {
                    $import_xls_file = $data['upload_data']['file_name'];
                } else {
                    $import_xls_file = 0;
                }
                $inputFileName = $path . $import_xls_file;
                try {
                    $inputFileType  = PHPExcel_IOFactory::identify($inputFileName);
                    $objReader      = PHPExcel_IOFactory::createReader($inputFileType);
                    $objPHPExcel    = $objReader->load($inputFileName);
                    $allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
                    $flag           = true;
                    $i              = 0;
                    foreach ($allDataInSheet as $value) {
                        if ($flag) {
                            $flag = false;
                            continue;
                        }
                        $inserdata[$i]['first_name'] = $value['A'];
                        $inserdata[$i]['last_name']  = $value['B'];
                        $inserdata[$i]['email']      = $value['C'];
                        $inserdata[$i]['contact_no'] = $value['D'];
                        $i++;
                    }
                    $result = $this->import->insert($inserdata);
                    if ($result) {
                        echo "Imported successfully";
                    } else {
                        echo "ERROR !";
                    }
                }
                catch (Exception $e) {
                    die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
                }
            } else {
                echo $error['error'];
            }
        }
        $this->load->view('import');
    }
}
?>

Create Model

Now go to application/models folder and create a one model name Export_model.php . After create this model put the below query in to model.

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Import_model extends CI_Model
{
    public function __construct()
    {
        $this->load->database();
    }
    public function insert($data)
    {
        $res = $this->db->insert_batch('import', $data);
        if ($res) {
            return TRUE;
        } else {
            return FALSE;
        }
    }
}
?>

Create Views

Now we need to create import.php, go to application/views/ folder and create Import.php file. Here put the below html code for showing list of product.

<!DOCTYPE html>
<html>
    <head>
        <title>Codeigniter Import Example - Torque Programming</title>
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
        <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
    </head>
    <body>
        <form action="<?php echo base_url();?>import/importFile" method="post" enctype="multipart/form-data">
            Upload excel file :
            <input type="file" name="uploadFile" value="" /><br />
            <br />
            <input type="submit" name="submit" value="Upload" />
        </form>
    </body>
</html>

Output