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

In this tutorial, I show you how to import CSV file data to the MySQL website for the CodeIgniter 4 project.

Contents

  1. Database configuration
  2. Create Table
  3. CSV file structure
  4. Model
  5. Route
  6. Controller
  7. View
  8. Run
  9. Conclusion


1. Database configuration

  • Open the .env file found at the root of the project.

NOTE - If a dot (.) is not added at first then rename the file to .env.

  • Remove # from the beginning of the database.default.hostname, database.default.database, database.default.username, database.default.password, and database.default.DBDriver.
  • Review the suspension 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 the app/Database/Migration/ folder from the root of the project.
  • Find the PHP file ending in create_users_table and open it.
  • Describe the structure of the table in up() method.
  • Using the down() method, remove the users table that calls for migration adjustment.
<?php namespace AppDatabaseMigrations;

use CodeIgniterDatabaseMigration;

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
stint, test@mail.com, indore, 1
Rakesh, test2@mail.com, Delhi, 1
Joy, tes3@mail.com, Bhopal, 1
  • The first row contains field names.

NOTE - skip the first line while recording.

  • While file import if the queue does not have all 4 fields then skip it.
  • If in your CSV file the first row contains data and you just need to clear the status of the controller.


4. Model

  • Create a Users.php file in the app/Models/ folder.
  • Open the file.
  • Specify the table name "users" for a variety of $table, key "id" in $primaryKey, Return type "array" to $ReturnType.
  • In $allowedFields Array specify field names - ['name', 'email', 'city', 'status'] that can be set during installation and renewal.

Complete Code

<?php 
namespace AppModels;

use CodeIgniterModel;

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 the app/Config/Routes.php file.
  • Define 2 -

                    / - Show file upload views and user list.

                    users/importFile - Used to upload a CSV file and import records.0

Completed Code

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


6. Controller

  • Create a UsersController.php file in the app/Controllers/ folder.
  • Open the file.
  • Import Users Model.
  • Create two modes -

                      index() - Select all records in the users table and assign to $data['users']. Upload users/index views and transfer $data.

                      importFile() - This method is called in the submission form to upload a 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 parameter name does not match the name of any uploaded files.
  2. max_size - Set maximum file upload size to KB -1024 (1 MB).
  3. ext_in - Valid file extensions - csv.

If the file is not approved then return to users/index view with verification response.

If the file is active then upload that file to the public/csvfile.

NOTE - the csvfile folder will be created if the folder is not in the public folder while uploading the file.


Read file data

Open the uploaded CSV file in reading mode.

Assign the total number of columns in a row - 4 to $numberOfFields. Change its value according to the number of columns in your CSV file.

Loop the file and count the full items in the $filedata Array and assign $num.

To skip the first row of the CSV file I added $i > 0. Remove this state if your CSV file contains data in the first row.

If $num == $numberOfFields then launch $importData_arr Array. Set a keyword with the name of the MySQL data field.


Enter the data

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

Retrieve the total number of records entered using the SESSION flash.

Completed Code

<?php namespace AppControllers;

use AppModelsUsers;

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 in app/Views/ and create index.php in the users folder.

Display the bootstrap alert message when 'message' SESSION exists. Also, set an alert section using Session 'alert-class'.

Download the confirmation service ConfigServices::validation() and assign it to $verification.

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

Create a file object and submit button. Show error in

 if not authorized.

Loop for $users to show user list in


8. Run

  • Navigate to the project using Command Prompt if you are on Windows or terminal if you are on Mac or Linux.
  • Then execute the command "php spark serve".
  • Run http://localhost:8080 in the web browser.

9. Conclusion

For example, I read the CSV file line by line and checked to see if all the fields were available or not. If it is found then I inserted it.

Enter the required verification to avoid duplication of data even before installation and check that the values ​​are in the required format.