How to add Data From Excel & CSV to mysql Using Codeigniter

In this CI excel or csv import article, we will teach you How to add Data From Excel & CSV to mysql Using Codeigniter. Excel and csv is the best method to Import record in a file and you can smoothly import data to Excel or CSV using Codeigniter excel library.

Codeigniter Import Excel,CSV File
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

Table of Contents

Download Codeigniter Project

Also Read:

In this phase we will download the latest version of Codeigniter, Go to this Download link Codeigniter download the new setup of codeigniter and unzip the setup in your local PC Mamp/wampp or xampp/htdocs/ . And change the download folder name “demo” or your desire folder name.

Basic Configurations

Now, we will fixed the some basic configuration on config.php file, so let’s move to application/config/config.php and open this file in a text editor.

Set Base URL like this

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

Download phpExcel Library

Download this excel library here : click here

In this step, we need to download phpExcel library from this given link, and extract into application/third_party directory. After the extract this library move to PHPExcel folder like application/third_party/PHPExcel and also transfer PHPExcel.php file to application/third_party/PHPExcel.php.

Next, we need to create Excel.php file into application/library, So move to application/library and create single file name Excel.php and add the below given code here.

Create Library

<?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

Now, we need to create database name demo/ your database name, so let’s connect your phpmyadmin and create the database with the name demo . After successfully create a database, you can use the below given sql query for creating a table in your DB.

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

Next, We need to link our codeigniter project to database. we need to move application/config/ and open database.php file in a text editor. After open the file in text editor, We need to setup database credential in this file like given below.

$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

Next we need to create a controller with name Import.php. In this controller we will create few method/function. We will make 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 Visit to application/models directory and create a one model with name Export_model.php . After create this model add the below gievn 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

Next we need to create import.php, visit to application/views/ folder and create Import.php file. Here add the below given html code for show list of product.

<!DOCTYPE html>
<html>
<head>
<title>Codeigniter Import Example</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>

Start Development server

For start development server, Go to the browser and visit below gievn the url.

http://localhost/demo/import

Conclusion

In this codeigniter excel/csv add data tutorial, you have successfully import csv or excel file using phpExcel library.

Post Views: 65

Download Codeigniter Project

Basic Configurations

Download phpExcel Library

Create Library

Create Database With Table

Setup Database Credentials

Create Controller

Create model

Create Views

Start Development server

Conclusion

Posted by Developer

0 Comments

Cancel reply

Download Codeigniter Project

Basic Configurations

Download phpExcel Library

Create Library

Create Database With Table

Setup Database Credentials

Create Controller

Create model

Create Views

Start Development server

Conclusion

Like it? Share with your friends!

0 Comments