CodeIgniter 4 Import CSV Data to MySQL Database Tutorial

In this tutorial, you will learn how to import CSV or excel data to MySQL database using Codeigniter 4 without using PHPexcel excel library.

Here are steps to import csv or excel file data into MySQL database:

Step 1: Setup Codeigniter 4 Project

In this step, you will download the latest version of Codeigniter 4, Go to this link https://codeigniter.com/download Download Codeigniter 4 fresh new setup and unzip the setup in your local system xampp/htdocs/ . And change the download folder name “demo”

Step 2: Basic Configurations

Edit app/config/app.php file and do some basic configuration in it:

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

Step 3: Create Table in Database

Run the following SQL query to create table in database that allow users to insert data into MySQL database from excel or csv file:

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: Setup Database

Edit app/Config/Database.php and setup database in it:

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

Step 5: Create a Controller

Create a controller name Import.php and methods in it that handle import CSV or excel file functionality:

<?php namespace App\Controllers;
use CodeIgniter\Controller;
use CodeIgniter\HTTP\RequestInterface;
use App\Models\Users;
class Import 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 6: Create a View

Create a form in view(application/views/import.php) file that allows users to choose to upload a CSV or Excel file to the database:

<!DOCTYPE html>
<html>
<head>
  <title>Codeigniter 4 Import Excel or CSV File into Database Example</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/importFile" 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: Define Routes

Define route in app/Config/Routes.php file to handle import csv or excel file requests:

$routes->get('/', 'Import::index');

Step 8: Test This Project

Run the following command to start development server:

php spark serve

Hit the following URL to test this project on browser:

http://localhost:8080

Conclusion

Import excel or csv file data into database in CodeIgniter 4. In this tutorial, you have learned how to import csv or excel file data into mysql database in codeigniter 4 app.

If you have any questions or thoughts to share, use the comment form below to reach us.

Recommended CodeIgniter 4 Tutorial