How to import excel data into mysql in codeigniter?

In this example we are going to show you how to How to import excel and CSV file in CodeIgniter framework MySQL PHP.

Table of Contents Show

1. Table structure
2. Configuration
4. Controller
6. Conclusion
How to import Excel data in Codeigniter?
How do you upload data from Excel to MySQL using PHP?
How do I import a CSV file into Codeigniter 3?
How do I export data from Codeigniter 3 to excel?

Here we using 3 files for import data in CodeIgniter framework MySQL PHP:

Crud.php Path: codeIgniter\application\controllers\Crud.php
Crud_model.php Path: codeIgniter\application\models\Crud_model.php
import_data.php Path: codeIgniter\application\views\import_data.php

Crud.php (Controller)

<?php 
	class Crud extends CI_Controller 
{
	public function __construct()
	{
		parent::__construct();/* call CodeIgniter's default Constructor */
		$this->load->database();/* load database libray manually */
		$this->load->model('Crud_model');/* load Model */
	}
	public function importdata()
	{ 
		$this->load->view('import_data');
		if(isset($_POST["submit"]))
		{
			$file = $_FILES['file']['tmp_name'];
			$handle = fopen($file, "r");
			$c = 0;//
			while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
			{
				$fname = $filesop[0];
				$lname = $filesop[1];
				if($c<>0){					/* SKIP THE FIRST ROW */
					$this->Crud_model->saverecords($fname,$lname);
				}
				$c = $c + 1;
			}
			echo "sucessfully import data !";
				
		}
	}
    	
}
?>

Crud_model.php (Model)

<?php
class Crud_model extends CI_Model 
{
	function saverecords($fname,$lname)
	{
		$query="insert into user values('$fname','$lname')";
		$this->db->query($query);
	}
	
}

insert.php (View)

<!DOCTYPE html>
<html>
<body>
<form enctype="multipart/form-data" method="post" role="form">
<div class="form-group">
<label for="exampleInputFile">File Upload</label>
<input type="file" name="file" id="file" size="150">
<p class="help-block">Only Excel/CSV File Import.</p>
</div>
<button type="submit" class="btn btn-default" name="submit" value="submit">Upload</button>
</form>
</body>
</html>

Run the program on your browser with URL:

http://localhost/codeIgniter/index.php/Crud/importdata

Here codeIgniter is my folder name. Put your folder name instead of codeIgniter.Rest of things are same.

CSV files are easier to maintain and can be used for export and import data in the web application.

For data import first, need to allow the users to browse and upload the file. In CodeIgniter, there is upload library available that makes it easier to handle file uploads.

In this tutorial, I show how you can import data of CSV file in the MySQL database table in the CodeIgniter 3 project.

Table structure
Configuration
Model
Controller
View
Conclusion

1. Table structure

I am using users table in the demonstration.

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(60) NOT NULL,
  `name` varchar(50) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `email` varchar(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

Navigate to application/config/database.php and define the Database connection.

$db['default'] = array(
 'dsn' => '',
 'hostname' => 'localhost',
 'username' => 'root', // Username
 'password' => '', // Password
 'database' => 'tutorial', // Database name
 '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
);

Default controller

Open application/config/routes.php and edit default_controller value to Users.

$route['default_controller'] = 'Users';

Load Database

To access the MySQL database require loading database library.

Open application/config/autoload.php and add the database in libraries array().

$autoload['libraries'] = array("database");

3. Model

Create a new Main_model.php file in application/models/ directory.

Define insertRecord() method to insert record in users table. It takes an Array as a parameter.

Check user already exit or not by username if not then define an Array $newuser and pass in $this->db->insert('users',$newuser).

Completed Code

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Main_model extends CI_Model {

  function insertRecord($record){
 
    if(count($record) > 0){
 
      // Check user
      $this->db->select('*');
      $this->db->where('username', $record[0]);
      $q = $this->db->get('users');
      $response = $q->result_array();
 
      // Insert record
      if(count($response) == 0){
        $newuser = array(
          "username" => trim($record[0]),
          "name" => trim($record[1]),
          "gender" => trim($record[2]),
          "email" => trim($record[3])
        );

        $this->db->insert('users', $newuser);
      }
 
    }
 
  }

}

4. Controller

Create a new Users.php in application/controllers/ directory.

Define two methods –

__construct() – Load url helper and Main_model model.
index() – On submit, button click check the file selected or not. If selected then set upload preference and load upload library.

Open the uploaded file in read mode.

I assigned the total number of columns in a row – 4 to $numberOfFields. Modify its value according to the number of columns in your CSV file.

Loop on the file and count total elements in $filedata Array and assign it to $num.

If $num == $numberOfFields then initialize $importData_arr Array.

To skip the first row created $skip variable and assigned 0 to it.

Loop on $importData_arr and call insertRecord() method if $skip != 0 to insert data in users Table where the pass the $userdata array.

Assign response to $data['response'] and load users_view view and pass $data.

Completed Code

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

class Users extends CI_Controller {

  public function __construct(){
    parent::__construct();

    // load base_url
    $this->load->helper('url');

    // Load Model
    $this->load->model('Main_model');
  }
 
  public function index(){

    // Check form submit or not 
    if($this->input->post('upload') != NULL ){ 
       $data = array(); 
       if(!empty($_FILES['file']['name'])){ 
         // Set preference 
         $config['upload_path'] = 'assets/files/'; 
         $config['allowed_types'] = 'csv'; 
         $config['max_size'] = '1000'; // max_size in kb 
         $config['file_name'] = $_FILES['file']['name'];

         // Load upload library 
         $this->load->library('upload',$config); 
 
         // File upload
         if($this->upload->do_upload('file')){ 
            // Get data about the file
            $uploadData = $this->upload->data(); 
            $filename = $uploadData['file_name'];

            // Reading file
            $file = fopen("assets/files/".$filename,"r");
            $i = 0;
            $numberOfFields = 4; // Total number of fields
            $importData_arr = array();
 
            while (($filedata = fgetcsv($file, 1000, ",")) !== FALSE) {
               $num = count($filedata );
               
               if($numberOfFields == $num){
                  for ($c=0; $c < $num; $c++) {
                     $importData_arr[$i][] = $filedata [$c];
                  }
               }
               $i++;
            }
            fclose($file);

            $skip = 0;

            // insert import data
            foreach($importData_arr as $userdata){

               // Skip first row
               if($skip != 0){
                  $this->Main_model->insertRecord($userdata);
               }
               $skip ++;
            }
            $data['response'] = 'successfully uploaded '.$filename; 
         }else{ 
            $data['response'] = 'failed'; 
         } 
      }else{ 
         $data['response'] = 'failed'; 
      } 
      // load view 
      $this->load->view('users_view',$data); 
    }else{
      // load view 
      $this->load->view('users_view'); 
    }

  }

}

5. View

Create a user_view.php file in application/views/ directory.

Create a <form > which contains a file element and a submit button. Use $response to display response either file import successfully or not.

Completed Code

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?>
<!DOCTYPE html>
<html lang="en">
 <head>
   <meta charset="utf-8">
 </head>
 <body>

   <?php 
   // Message
   if(isset($response)){
     echo $response;
   }
   ?>

   <form method='post' action='' enctype="multipart/form-data">
     <input type='file' name='file' >
     <input type='submit' value='Upload' name='upload'>
   </form>

  </body>
</html>

6. Conclusion

Using upload library to upload file to the server and insert records to the MySQL table while reading the file data.

You can also view this tutorial to know how to import CSV file data to the MySQL database in the CodeIgniter 4 project.

If you found this tutorial helpful then don't forget to share.

How to import Excel data in Codeigniter?

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

How do you upload data from Excel to MySQL using PHP?

To Import Excel File Into MySQL It Takes Only Two Steps:-.

Make a HTML file and define markup. We make a HTML file and save it with a name import.html. ... .

Make a PHP file to import data into mysql database. We make a PHP file and save it with a name import_file.php..

How do I import a CSV file into Codeigniter 3?

Import CSV file into database using CodeIgniter.

Step1: Download CodeIgniter3 https://codeigniter.com/download..

Step2: Create a database named csvimport CREATE DATABASE csvimport;.

How do I export data from Codeigniter 3 to excel?

So let's start..

Step1: Create MySQL Database Table. As we will cover this tutorial with live example, so first we will create MySQL database table emp . ... .

How to import excel data into mysql in codeigniter?

Crud.php (Controller)

Crud_model.php (Model)

insert.php (View)

Contents

1. Table structure

2. Configuration

3. Model

4. Controller

5. View

6. Conclusion

How to import Excel data in Codeigniter?

How do you upload data from Excel to MySQL using PHP?

How do I import a CSV file into Codeigniter 3?

How do I export data from Codeigniter 3 to excel?

Pos Terkait

Periklanan

BERITA TERKINI

Toplist Popular

Periklanan

Terpopuler

Token Data

Periklanan

Tentang Kami

Legal

Dukungan

Social