How to Import CSV file data to MySQL in CodeIgniter 4

CSV is a widely used format for exchanging data between applications.

The database and data dumps should be in a proper format to import data smoothly and without any issue.

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

How to Import CSV file data to MySQL in CodeIgniter 4

Download

Database configuration
Create Table
CSV file structure
Model
Route
Controller
View
Run
Output
Conclusion

1. Database configuration

Open .env file which is available at the project root.

NOTE – If dot (.) not added at the start then rename the file to .env.

Remove # from start of database.default.hostname, database.default.database, database.default.username, database.default.password, database.default.DBDriver, database.default.DBPrefix, and database.default.port.
Update the configuration and save it.

database.default.hostname = 127.0.0.1
database.default.database = codeigniterdb
database.default.username = root
database.default.password = root
database.default.DBDriver = MySQLi
database.default.DBPrefix =
database.default.port = 3306

2. Create Table

Create a new table users using migration.

php spark migrate:create create_users_table

Now, navigate to app/Database/Migrations/ folder from the project root.
Find a PHP file that ends with create_users_table and open it.
Define the table structure in the up() method.
Using the down() method delete users table which calls when undoing migration.

<?php namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateUsersTable extends Migration
{
    public function up() {
       $this->forge->addField([
          'id' => [
              'type' => 'INT',
              'constraint' => 5,
              'unsigned' => true,
              'auto_increment' => true,
          ],
          'name' => [
              'type' => 'VARCHAR',
              'constraint' => '100',
          ],
          'email' => [
              'type' => 'VARCHAR',
              'constraint' => '100',
          ],
          'city' => [
              'type' => 'VARCHAR',
              'constraint' => '100',
          ],
          'status' => [
              'type' => 'INT',
              'constraint' => '2',
          ],
       ]);
       $this->forge->addKey('id', true);
       $this->forge->createTable('users');
    }

    //--------------------------------------------------------------------

    public function down() {
       $this->forge->dropTable('users');
    }
}

Run the migration –

php spark migrate

3. CSV file structure

In the example, I am using the following structure –

Name, Email, City, Status
Yogesh singh, yogesh@makitweb.com, Bhopal, 1
Sonarika Bhadoria, bsonarika@makitweb.com, Delhi, 1
Ankit singh, ankitsingh@makitweb.com, Bhopal, 1

The first row contains field names.

NOTE – I am skipping the first row while record inserting.

While file importing if a row does not contain all 4 fields then I am skipping it.

If in your CSV file the first row also contains data then just need to remove a condition in the controller.

4. Model

Create Users Model –

php spark make:model Users

Open app/Models/Users.php file.
In $allowedFields Array specify field names – ['name', 'email','city','status'] that can be set during insert and update.

Completed Code

<?php

namespace App\Models;

use CodeIgniter\Model;

class Users extends Model
{
     protected $DBGroup = 'default';
     protected $table = 'users';
     protected $primaryKey = 'id';
     protected $useAutoIncrement = true;
     protected $insertID = 0;
     protected $returnType = 'array';
     protected $useSoftDeletes = false;
     protected $protectFields = true;
     protected $allowedFields = ['name', 'email','city','status'];

     // Dates
     protected $useTimestamps = false;
     protected $dateFormat = 'datetime';
     protected $createdField = 'created_at';
     protected $updatedField = 'updated_at';
     protected $deletedField = 'deleted_at';

     // Validation
     protected $validationRules = [];
     protected $validationMessages = [];
     protected $skipValidation = false;
     protected $cleanValidationRules = true;

     // Callbacks
     protected $allowCallbacks = true;
     protected $beforeInsert = [];
     protected $afterInsert = [];
     protected $beforeUpdate = [];
     protected $afterUpdate = [];
     protected $beforeFind = [];
     protected $afterFind = [];
     protected $beforeDelete = [];
     protected $afterDelete = [];
}

5. Route

Open app/Config/Routes.php file.
Define 2 routes –
- / – Display file upload view and user list.
- users/importFile – It is used to upload a CSV file and insert records.

Completed Code

$routes->get('/', 'UsersController::index');
$routes->post('users/importFile', 'UsersController::importFile');

6. Controller

Create UsersControllerController –

php spark make:controller UsersController

Open app/Controllers/UsersController.php file.
Import Users Model.
Create two methods –
- index() – Select all records from the users table and assign to $data['users']. Load users/index view and pass $data.
- importFile() – This method is called on form submit to upload the file and import data.

File Upload

Set file validation –

'file' => 'uploaded[file]|max_size[file,1024]|ext_in[file,csv],'

1. uploaded – Fails if the name of the parameter does not match the name of any uploaded files.
2. max_size – Set maximum file upload size in KB -1024 (1 MB).
3. ext_in – Valid file extensions – csv.

If the file is not validated then return to the users/index view with validation response.

If the file is valid then upload the file to public/csvfile location.

NOTE – csvfile folder will be created if the folder does not exist in the public folder while file uploading.

Read file data

Open the uploaded CSV 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.

To skip the first row of the CSV file I have added $i > 0 condition. Remove this condition if your CSV file also contains data on the 1st row.

If $num == $numberOfFields then initialize $importData_arr Array. Set the key name with the MySQL database field name.

Insert data

Loop on the $importData_arr Array and check if email already exists in the users table. If not exist then insert a new record and increment $count by 1.

Return the total number of records inserted using SESSION flash.

Completed Code

<?php namespace App\Controllers;

use App\Models\Users;

class UsersController extends BaseController{

   public function index(){
      ## Fetch all records
      $users = new Users();
      $data['users'] = $users->findAll();

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

   // 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('/'); 
   }
}

7. View

Create a users folder at app/Views/ and create index.php in users folder.

Display bootstrap alert message if 'message' SESSION exists. Also, set alert class using 'alert-class' Session.

Load validation service \Config\Services::validation() and assign it to $validation.

Create <form method="post" action="<?=site_url('users/importFile')?>" enctype="multipart/form-data">.

Create a file element and submit button. Display error in <div > if not validated.

Loop on $users to display users list in <table >.

Completed Code

<!DOCTYPE html>
<html>
<head>
   <title>How to Import CSV file data to MySQL in CodeIgniter 4</title>

   <link rel="stylesheet" type="text/css" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>

   <div class="container">

     <div class="row">
       <div class="col-md-12">
          <?php 
          // Display Response
          if(session()->has('message')){
          ?>
             <div class="alert <?= session()->getFlashdata('alert-class') ?>">
                <?= session()->getFlashdata('message') ?>
             </div>
          <?php
          }
          ?>

          <?php $validation = \Config\Services::validation(); ?>

          <form method="post" action="<?=site_url('users/importFile')?>" enctype="multipart/form-data">

             <?= csrf_field(); ?>
             <div class="form-group">
                <label for="file">File:</label>

                <input type="file" class="form-control" id="file" name="file" />
                <!-- Error -->
                <?php if( $validation->getError('file') ) {?>
                <div class='alert alert-danger mt-2'>
                   <?= $validation->getError('file'); ?>
                </div>
                <?php }?>

             </div>

             <input type="submit" class="btn btn-success" name="submit" value="Import CSV">
          </form>
       </div>
     </div>

     <div class="row">

        <!-- Users list -->
        <div class="col-md-12 mt-4" >

           <h3 class="mb-4">Users List</h3>
           <table width="100%">
              <thead>
                 <tr>
                    <th>ID</th>
                    <th>Name</th>
                    <th>Email</th>
                    <th>City</th>
                    <th>Status</th>
                 </tr>
              </thead>
              <tbody>
                 <?php 
                 if(isset($users) && count($users) > 0){
                     foreach($users as $user){
                 ?>
                       <tr>
                          <td><?= $user['id'] ?></td>
                          <td><?= $user['name'] ?></td>
                          <td><?= $user['email'] ?></td>
                          <td><?= $user['city'] ?></td>
                          <td><?= $user['status'] ?></td>
                       </tr>
                 <?php
                     }
                 }else{
                 ?>
                     <tr>
                        <td colspan="5">No record found.</td>
                     </tr>
                 <?php
                 }
                 ?>
             </body>
           </table>
        </div>

     </div>
   </div>

</body>
</html>

8. Run

Navigate to the project using Command Prompt if you are on Windows or terminal if you are on Mac or Linux, and
Execute “php spark serve” command.

php spark serve

Run http://localhost:8080 in the web browser.

9. Output

View Output

10. Conclusion

In the example, I read the CSV file row by row and check if all fields are available or not. If available then I inserted it.

Add required validation to avoid duplicacy of data and before inserting also check if values are in the required format.

You can view this tutorial to know how to export data in CSV format in CodeIgniter 4.

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

How to Import CSV file data to MySQL in CodeIgniter 4

Contents

1. Database configuration

2. Create Table

3. CSV file structure

4. Model

5. Route

6. Controller

7. View

8. Run

9. Output

10. Conclusion

Leave a Comment Cancel reply