Import Excel sheet .csv file to database using php Codeigniter | 2my4edge

07 July 2016

Import Excel sheet .csv file to database using php Codeigniter

Importing bulk data through excel sheet .csv (comma delimited) is one of the useful function to reduce the one by one data updating. and sure that excel sheet format should be .csv (comma delimited) format, while saving excel sheet, save it in .csv (comma delimited) format. and lets see the code for that.

import excel sheet using php codeignter
DOWNLOAD                                                   LIVE DEMO

DATABASE DETAILS

DATABASE NAME : 2my4edge (in download file 'test')

TABLE NAME : import
CREATE TABLE `import` (
  `id` int(11) NOT NULL,
  `name` varchar(250) NOT NULL,
  `email` varchar(250) NOT NULL,
  `created_date` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



SAMPLE EXCEL SHEET FORMAT

Sample excel sheet format for import

Then save that Excel sheet in .csv (Comma delimited) format. Column A is for 'name' and B is for 'email'.

CONTROLLER FILE

Uploadcsv.php
<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Uploadcsv extends CI_Controller {

public function __construct()
{
    parent::__construct();
    $this->load->helper('url');                    
    $this->load->model('Welcome_model','welcome');
}

public function index()
{
    $this->data['view_data']= $this->welcome->view_data();
  $this->load->view('excelimport', $this->data, FALSE);
}

public function importbulkemail(){
    $this->load->view('excelimport');
}

public function import(){
 if(isset($_POST["import"]))
  {
      $filename=$_FILES["file"]["tmp_name"];
      if($_FILES["file"]["size"] > 0)
        {
          $file = fopen($filename, "r");
           while (($importdata = fgetcsv($file, 10000, ",")) !== FALSE)
           {
                  $data = array(
                      'name' => $importdata[0],
                      'email' =>$importdata[1],
                      'created_date' => date('Y-m-d'),
                      );
           $insert = $this->welcome->insertCSV($data);
           }                    
          fclose($file);
$this->session->set_flashdata('message', 'Data are imported successfully..');
redirect('uploadcsv/index');
        }else{
$this->session->set_flashdata('message', 'Something went wrong..');
redirect('uploadcsv/index');
    }
  }
}

}


VIEW FILE
excelimport.php
<?php if($this->session->flashdata('message')){?>
          <div align="center" class="alert alert-success">      
            <?php echo $this->session->flashdata('message')?>
          </div>
        <?php } ?>

<br><br>

<div align="center">
<form action="<?php echo base_url(); ?>index.php/uploadcsv/import" 
method="post" name="upload_excel" enctype="multipart/form-data">
<input type="file" name="file" id="file">
<button type="submit" id="submit" name="import">Import</button>
</form>
<br>
<br>
<a href="<?php echo base_url(); ?>sample.csv"> Sample csv file </a>
<br><br>

<div style="width:80%; margin:0 auto;" align="center">
<table id="t01">
  <tr>
    <th>Name</th>
    <th>Email</th>
    <th>Date</th>
  </tr>
<?php
if(isset($view_data) && is_array($view_data) && count($view_data)): $i=1;
foreach ($view_data as $key => $data) { 
?>
  <tr>
    <td><?php echo $data['name'] ?></td>
    <td><?php echo $data['email'] ?></td>
    <td><?php echo $data['created_date'] ?></td>
  </tr>
  <?php } endif; ?>
</table>
</div>
</div>


MODEL FILE
Welcome_model.php
<?php
class Welcome_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function insertCSV($data)
            {
                $this->db->insert('import', $data);
                return TRUE;
            }



    public function view_data(){
        $query=$this->db->query("SELECT im.*
                                 FROM import im 
                                 ORDER BY im.id DESC
                                 limit 10");
        return $query->result_array();
    }

}

That's it. it will import the .csv file to your mysql database. i hope you like it. If you have any Queries comment below.

RELATED POSTS :

16 comments:

  1. I like the way you helped me this time!! thank you very Much

    ReplyDelete
  2. similarly i want to download a csv template from server and fill then upload. How to do it?

    ReplyDelete
  3. function importcsv() {

    $res = $this->getAllLocation();
    ini_set('auto_detect_line_endings', true);
    if ($_FILES ['csv_name'] != '') {
    $adminId=$this->session->userdata('admin_id');
    //$language=$this->input->post('selLanguage');
    //setSAActivityLogs('Transaction_activity','SAKeywordscv',"upload By :- ".$adminId);
    $error = array();
    $line_error = 1;
    $base_url = base_url();
    $file_name = time() . $_FILES ["csv_name"] ["name"];

    move_uploaded_file($_FILES ["csv_name"] ["tmp_name"], "./uploads/keywordcsv/" . $file_name);

    $upfile = "./uploads/keywordcsv/$file_name";
    $fp = fopen($upfile, "r");
    $n = 0;
    $k = 0;
    $duplicate = array();
    $flag = 0;
    while ($line1 = fgets($fp)) {

    $importData = str_getcsv($line1, ',', '"');

    if ($importData [0] != '') {
    if ($n > 0) {
    $txtid = $importData[0];
    $location_name = $importData[1];
    //echo $location_name;exit;
    for ($i = 0; $i < count($res); $i++)
    {
    $res = $this->getAllLocation();
    if ($res[$i]->location_name == $location_name) {
    $duplicate[$k] = $location_name;
    $k++;
    $flag = 1;
    }
    }
    if ($flag == 0)
    {
    $data = array('county_id' => $txtid,
    'location_name' => $location_name,
    'status' => 1);
    // print_r($data);exit;
    $this->db->insert('data_location', $data);
    }
    $flag = 0;
    }
    $n ++;
    }
    }
    $this->create_log(" Location Csv", "Location","upload By :- ".$adminId);
    $this->session->set_userdata('duplicate', $duplicate);
    //$str=implode(" ", $duplicate);
    return true;
    }
    return false;
    }

    ReplyDelete
  4. how can i set get data from column 2?

    ReplyDelete
  5. the code is not working .garbage values are inserting to the table

    ReplyDelete
  6. Next type the path and filename where you want the merged file to appear. Hit enter, and windows will create a combined file! Easy as that.excel reporting dashboard

    ReplyDelete
  7. Nice job dude.
    Its work perfectely without any error..

    ReplyDelete
  8. my excel import code is working in local but in online my code is not working
    displays page not found 404 error
    kindly clarify

    ReplyDelete
  9. Great blog. You put Good stuff. All the topics were explained briefly. So quickly understand for me. I am waiting for your next fantastic blog. Thanks for sharing. Any course related details learn...
    Php course in chennai

    ReplyDelete
  10. my excel code is working in my localhost but when i upload it to my server it shows 404 error found and the page not found. I checked from my end. Kindly help me to sort it out.

    ReplyDelete
  11. A PHP Error was encountered
    Severity: Notice
    Message: Undefined property: Uploadcsv::$db
    Filename: core/Model.php
    Line Number: 52

    Fatal error: Call to a member function query() on a non-object in C:\xampp3\htdocs\exceli\application\models\Welcome_model.php on line 18

    ReplyDelete
  12. Any terrible string or procedure can cause such click here a fitting activity easily, even SQLite library can't help you in this circumstance.

    ReplyDelete

^