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


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

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 which 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, [email protected], Bhopal, 1
Sonarika Bhadoria, [email protected], Delhi, 1
Ankit singh, [email protected], 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. 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 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


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.

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

Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request.

Related posts:

Spread the love

Related Posts