Codeigniter 4 Import CSV, Excel to MySQL Database

Import CSV file data into MySQL database using PHP CodeIgniter 4 example; In this tutorial, i will show you how to import data from csv or excel file into MySql database PHP codeigniter 4 app. And as well learn how insert bulk data into mysql database using PHP codeigniter 4 app.

If you have any csv and excel file and there is a lot of data in it. And you want to insert that data into MySQL database. So in this tutorial you will get a step by step guide with the help of which you can insert data from csv and excel files into database in your codeigniter 4 app.

How to import excel file data into mysql database in php codeigniter 4 tutorial; In this example, I will create simple form and controller, where form is used to send data to controller in PHP codeigniter 4 app and controller method is used to read csv or excel file & store data into mysql database.

How to Import Data from Excel Or CSV to MySQL Database in PHP Codeigniter 4

Install Codeigniter 4 Application
Basic App Configurations
Create Database and Table
Connect App to Database
Create Model Class
Create Controller Class
Create View
Setup Routes
Start Development server

Step 1 – Install Codeigniter 4 Application

First of all, you need to ownload the latest version of Codeigniter 4. So, visit this link https://codeigniter.com/download Download Codeigniter 4 app and unzip the setup in your local system xampp/htdocs/ .

Note that, please change the download folder name “demo”.

Step 2 – Basic App Configurations

Now, you need to some basic configuration on the app/config/app.php file, so let’s go to application/config/config.php and open this file on text editor.

Set Base URL like this

public $baseURL = 'http://localhost:8080';
To
public $baseURL = 'http://localhost/demo/';

Step 3 – Create Database and Table

Create a database and table by executing the following SQL query:

CREATE DATABASE demo;

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `status` varchar(255) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step 4 – Connect App to Database

To connect your codeigniter 4 app to the database. So, visit app/Config/ directory and open Database.php. Then add the databasae details like below into database.php file:

public $default = [
        'DSN'      => '',
        'hostname' => 'localhost',
        'username' => 'test',
        'password' => '4Mu99BhzK8dr4vF1',
        'database' => 'demo',
        'DBDriver' => 'MySQLi',
        'DBPrefix' => '',
        'pConnect' => false,
        'DBDebug'  => (ENVIRONMENT !== 'development'),
        'cacheOn'  => false,
        'cacheDir' => '',
        'charset'  => 'utf8',
        'DBCollat' => 'utf8_general_ci',
        'swapPre'  => '',
        'encrypt'  => false,
        'compress' => false,
        'strictOn' => false,
        'failover' => [],
        'port'     => 3306,
	];

Step 5 – Create Model Class

Create Users.php file. So, visit app/Models directory and create Model.php.Then add the following code into it:

Step 6 – Create Controller Class

Create ImportDataController.php file. So, visit app/Controllers directory and create ImportDataController.php.Then add the following code into it:

<?php namespace App\Controllers;
use CodeIgniter\Controller;
use CodeIgniter\HTTP\RequestInterface;
use App\Models\Users;

class ImportDataController extends Controller
{

    public function index() {
        return view('import');
    }

   // File upload and Insert records
   public function importFile(){

      // Validation
      $input = $this->validate([
         'file' => 'uploaded[file]|max_size[file,1024]|ext_in[file,csv],'
      ]);

      if (!$input) { // Not valid
         $data['validation'] = $this->validator;

         return view('users/index',$data); 
      }else{ // Valid

         if($file = $this->request->getFile('file')) {
            if ($file->isValid() && ! $file->hasMoved()) {

               // Get random file name
               $newName = $file->getRandomName();

               // Store file in public/csvfile/ folder
               $file->move('../public/csvfile', $newName);

               // Reading file
               $file = fopen("../public/csvfile/".$newName,"r");
               $i = 0;
               $numberOfFields = 4; // Total number of fields

               $importData_arr = array();

               // Initialize $importData_arr Array
               while (($filedata = fgetcsv($file, 1000, ",")) !== FALSE) {
                  $num = count($filedata);

                  // Skip first row & check number of fields
                  if($i > 0 && $num == $numberOfFields){ 
                     
                     // Key names are the insert table field names - name, email, city, and status
                     $importData_arr[$i]['name'] = $filedata[0];
                     $importData_arr[$i]['email'] = $filedata[1];
                     $importData_arr[$i]['city'] = $filedata[2];
                     $importData_arr[$i]['status'] = $filedata[3];

                  }

                  $i++;

               }
               fclose($file);
 
               // Insert data
               $count = 0;
               foreach($importData_arr as $userdata){
                  $users = new Users();

                  // Check record
                  $checkrecord = $users->where('email',$userdata['email'])->countAllResults();

                  if($checkrecord == 0){

                     ## Insert Record
                     if($users->insert($userdata)){
                         $count++;
                     }
                  }

               }

               // Set Session
               session()->setFlashdata('message', $count.' Record inserted successfully!');
               session()->setFlashdata('alert-class', 'alert-success');

            }else{
               // Set Session
               session()->setFlashdata('message', 'File not imported.');
               session()->setFlashdata('alert-class', 'alert-danger');
            }
         }else{
            // Set Session
            session()->setFlashdata('message', 'File not imported.');
            session()->setFlashdata('alert-class', 'alert-danger');
         }

      }

      return redirect()->route('/'); 
   }

}

Step 7 – Create View

Create home.php file. So, visit app/views directory and create home.php.Then add the following code into it:

<!DOCTYPE html>
<html>
<head>
  <title>Codeigniter 4 Import Excel or CSV File into Database Example - Laratutorials.com</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-file" 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>

Step 7 – Setup Routes

To define a route, So, visit app/Config/ directory and open Routes.php file. Then add the following routes into it:

$routes->setDefaultController('ImportDataController');
$routes->get('/', 'ImportDataController::index');
$routes->post('import-file', 'ImportDataController::importFile');

Step 8 – Start Development server

Execute the following command into command prompt or terminal to start the codeigniter 4 application:

php spark serve

Then visit your web browser and hit the following url on it:

http://localhost/demo/

OR

http://localhost:8080/

Conclusion

Export data to excel in codeigniter 4 using phpexcel example; In this tutorial, You have learned how to export data from database using phpexcel library in codeigniter 4 app.