Codeigniter 4 Import CSV/Excel to MySQL Database
In this article, we will explain to you how to import CSV or excel to MySQL database in CodeIgniter 4(Codeigniter 4 Import CSV/Excel to MySQL Database). we can easily import data into MySQL database using CSV or excel sheet in CodeIgniter 4.
In this example, we need a CSV or excel file. which is used to import data into the database. here we also check file validation in CodeIgniter 4. so you can see our following example.
Overview
Step 1: Download Codeigniter
Step 2: Basic Configurations
Step 3: Create a Database in table
Step 4: Connect to Database
Step 5: Create Controller and Model
Step 6: Create Views Files
Step 7: Run The Application
Step 1: Download Codeigniter
If you want to download or install the latest version of CodeIgniter 4 then you can go to Codeigniter’s official site and download the latest version of Codeigniter 4. after the downloaded you can configure in “xampp/htdocs/” directory.
Step 2: Basic Configurations
If you want to Basic Configurations in your project then you can below Url.
Codeigniter 4 Removing Index.Php From Url
Step 3: Create a Database in table
In this step, We will create the database and table.
1 2 3 4 5 6 7 8 9 | CREATE TABLE IF NOT EXISTS `students` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(64) NOT NULL, `last_name` varchar(64) NOT NULL, `address` text NOT NULL, `email` varchar(64) NOT NULL, `mobile` varchar(12) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1; |
Step 4: Connect to Database
Go to the “app/Config/Database.php” folder and open the database.php file some changes in this file like hostname, database username, database password, and database name.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | public $default = [ 'DSN' => '', 'hostname' => 'localhost', 'username' => 'root', 'password' => '', 'database' => 'codeigniter4_jqurey_validation', '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 Controller and Model
In this step, we will create the “Student.php” controller and the “StudentModel.php” model.
app/Controllers/Student.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 | <?php namespace App\Controllers; use CodeIgniter\Controller; use App\Models\StudentModel; use CodeIgniter\HTTP\RequestInterface; class Student extends Controller { public function __construct() { helper(['form', 'url']); } public function index() { return view('add'); } public function importFile() { $rules = [ 'image' => [ 'uploaded[file]', 'max_size[file,1024]', 'ext_in[image,csv]', ], ]; if (!$this->validate($rules)) { return view('add', ['validation' => $this->validator]); } else { $file = $this->request->getFile('file'); if($file){ $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 = 5; // Total number of fields $importData_arr = array(); while (($filedata = fgetcsv($file, 1000, ",")) !== FALSE) { $num = count($filedata); if($i > 0 && $num == $numberOfFields){ $importData_arr[$i]['first_name'] = $filedata[0]; $importData_arr[$i]['last_name'] = $filedata[1]; $importData_arr[$i]['email'] = $filedata[2]; $importData_arr[$i]['address'] = $filedata[3]; $importData_arr[$i]['mobile'] = $filedata[4]; } $i++; } fclose($file); $image->move(WRITEPATH . 'uploads'); // Insert data $count = 0; foreach($importData_arr as $studentData){ // Check record $model = new StudentModel(); $checkrecord = $model->where('email',$studentData['email'])->countAllResults(); if($checkrecord == 0){ ## Insert Record if($model->insert($studentData)){ $count++; } } return redirect()->to( base_url('student') ); } }else{ echo "File not imported"; } } } } } ?> |
app/Models/StudentModel.php
1 2 3 4 5 6 7 8 9 10 11 12 | <?php namespace App\Models; use CodeIgniter\Database\ConnectionInterface; use CodeIgniter\Model; class StudentModel extends Model { protected $table = 'Students'; protected $allowedFields = ['first_name','last_name','address','email', 'mobile']; } ?> |
Step 6: Create Views Files
Finally, we will create the add.php in the app/views directory.
app/views/add.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | <!DOCTYPE html> <html> <head> <title>Codeigniter 4 Import Excel/CSV File into Database Example - XpertPhp</title> <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.js"></script> </head> <body> <div class="container"> <div class="row"> <div class="col-md-9"> <?= \Config\Services::validation()->listErrors(); ?> <form method="post" name="frmAddStudent" id="frmAddStudent" action="<?php echo site_url('student/importFile');?>" enctype="multipart/form-data"> <div class="form-group"> <label for="file">Upload CSV/Excel file</label> <input type="file" name="file" class="form-control" id="file" /> </div> <div class="form-group"> <input type="submit" value="Upload" name="btnadd" id="btnadd" class="btn btn-success" /> </div> </form> </div> </div> </div> </body> </html> |
Step 7: Run The Application
We can start the server and run the codeigniter 4 application using the below command.
1 | php spark serve |
Now we will run our example using the below Url in the browser.
1 | http://localhost:8080/student |
If you liked this article, you can also download it through our Github Repository.
