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 :

9 comments:

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

    ReplyDelete
  2. thankyou , you help me , thank you

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

    ReplyDelete
  4. 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
  5. how can i set get data from column 2?

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

    ReplyDelete
  7. 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

^