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.

Download

 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, and database.default.DBDriver.
• Update the configuration and save it.

database.default.hostname = 127.0.0.1
database.default.database = testdb
database.default.username = root
database.default.password = 
database.default.DBDriver = MySQLi

---

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.php file in app/Models/ folder.
• Open the file.
• Specify table name "users" in $table variable, primary key "id" in $primaryKey, Return type "array" in $returnType.
• 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 $table = 'users'; 
    protected $primaryKey = 'id';

    protected $returnType = 'array';

    protected $allowedFields = ['name', 'email','city','status'];
    protected $useTimestamps = false;

    protected $validationRules = [];
    protected $validationMessages = [];
    protected $skipValidation = false;

}

---

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 UsersController.php file in app/Controllers/ folder.
• Open the 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. 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

Video Player

00:00

01:37

---

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.

Share this post