A CSV is a comma-separated values file, helpful in storing data in a tabular format. CSV is widely used with spreadsheet programs, .csv extension is a vindication of CSV file.
It offers an eloquent way to transfer information between various applications and is significantly used with Microsoft Excel or Google Spreadsheets.
Throughout this extensive guide, we will teach you how to import CSV file data to the MySQL database in Codeigniter 4 application from the total beginning.
Data is everywhere; it is highly possible that most of the time, you want to import excel file or CSV file data to MySQL database in Codeigniter; if you know how it is done, it is ok, but if you don’t know how to build this feature then it becomes severe pain.
Therefore, we are about to explain how to import data from an excel file to MySQL in CodeIgniter dynamically. We will share specific instructions that will help you quench your thirst to create a simple functionality to import CSV file to MySQL database Codeigniter.
Codeigniter 4 Import Excel/CSV File Data into MySQL Database Example
- Step 1: Download Codeigniter Project
- Step 2: Configure Error Handling
- Step 3: Generate User Table in Database
- Step 4: Connect to Database
- Step 5: Create Sample CSV File
- Step 6: Create Model
- Step 7: Set Up Controller
- Step 8: Set Up Routes
- Step 9: Create Codeigniter View File
- Step 10: Start CI Application
Download Codeigniter Project
Initiate the first step creating the Codeigniter web application, and there are two straightforward ways.
You can download through the command-line interface and execute the below command but ensure that you have Composer configured on your machine.
composer create-project codeigniter4/appstarterOr, you can directly download the Codeigniter app right from their official website.
After downloading the app, unzip the project and rename.
Configure Error Handling
Next, set display_errors to 1 in app/Config/Boot/production.php, it makes the error handling process profoundly facile.
ini_set('display_errors', '1');Generate User Table in Database
We have provided a SQL query below; I don’t have to tell you what it does, right? So, open the SQL query console in PHPMyAdmin, enter this query; after successfully executing this query, you will add a new table into the MySQL database.
CREATE TABLE students (
id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
name varchar(150) NOT NULL COMMENT 'Name',
email varchar(255) NOT NULL COMMENT 'Email',
phone varchar(250) NOT NULL COMMENT 'Phone',
created_at varchar(20) NOT NULL COMMENT 'Date Created',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Student table' AUTO_INCREMENT=1;Connect to Database
To store the CSV file data into the database, open app/Config/Database.php, thereafter add database details like database name, username and password.
public $default = [
'DSN' => '',
'hostname' => 'localhost',
'username' => 'root',
'password' => '',
'database' => 'codeigniter_db',
'DBDriver' => 'MySQLi',
'DBPrefix' => '',
'pConnect' => false,
'DBDebug' => (ENVIRONMENT !== 'development'),
'cacheOn' => false,
'cacheDir' => '',
'charset' => 'utf8',
'DBCollat' => 'utf8_general_ci',
'swapPre' => '',
'encrypt' => false,
'compress' => false,
'strictOn' => false,
'failover' => [],
'port' => 3306,
];You may or may not see “Unable to connect database: Codeigniter error”. Don’t worry; we will get fixed this for you. Add either of the value in the hostname based on your local server (MAMP or XAMP).
# MAMP
public $default = [
'hostname' => '/Applications/MAMP/tmp/mysql/mysql.sock',
]
# XAMP
public $default = [
'hostname' => '/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock',
]Create Sample CSV File
Don’t worry. If you don’t have a CSV file, you can copy the following comma-separated code, paste it into the code editor and save the file with the .csv extension.
name, email, phone, created_at
Sam Coury, christine1998@yahoo.com, 772-267-3636, 2021-02-02
Chris Ellis, ellis@yahoo.com, 270-562-2304, 2020-03-03
Cameron Finley, cameron@rediff.com, 813-416-4979, 2019-01-05
Howard Schlueter, schlueter@gmail.com, 828-207-1592, 2018-05-02
Jordan Tafoya, annabell2001@hotmail.com, 512-596-3622, 2017-05-11Create New Model
The idea of the Model revolves around holding the prototype of the database table schema, consequently create StudentModel.php in the app/Models folder.
Update app/Models/StudentModel.php file.
<?php
namespace App\Models;
use CodeIgniter\Database\ConnectionInterface;
use CodeIgniter\Model;
class StudentModel extends Model
{
protected $table = 'students';
protected $allowedFields = [
'name',
'email',
'phone',
'created_at'
];
}Generate and Set Up Controller
Now, in this section, we have to create and configure a controller file, hence create StudentController.php in the app/Controllers folder. This file contains the single class, which becomes the locus of various functions, which performs different tasks under one roof.
Update app/Controllers/StudentController.php file.
<?php
namespace App\Controllers;
use CodeIgniter\Controller;
use CodeIgniter\HTTP\RequestInterface;
use App\Models\StudentModel;
class StudentController extends Controller
{
public function index()
{
return view('index');
}
public function importCsvToDb()
{
$input = $this->validate([
'file' => 'uploaded[file]|max_size[file,2048]|ext_in[file,csv],'
]);
if (!$input) {
$data['validation'] = $this->validator;
return view('index', $data);
}else{
if($file = $this->request->getFile('file')) {
if ($file->isValid() && ! $file->hasMoved()) {
$newName = $file->getRandomName();
$file->move('../public/csvfile', $newName);
$file = fopen("../public/csvfile/".$newName,"r");
$i = 0;
$numberOfFields = 4;
$csvArr = array();
while (($filedata = fgetcsv($file, 1000, ",")) !== FALSE) {
$num = count($filedata);
if($i > 0 && $num == $numberOfFields){
$csvArr[$i]['name'] = $filedata[0];
$csvArr[$i]['email'] = $filedata[1];
$csvArr[$i]['phone'] = $filedata[2];
$csvArr[$i]['created_at'] = $filedata[3];
}
$i++;
}
fclose($file);
$count = 0;
foreach($csvArr as $userdata){
$students = new StudentModel();
$findRecord = $students->where('email', $userdata['email'])->countAllResults();
if($findRecord == 0){
if($students->insert($userdata)){
$count++;
}
}
}
session()->setFlashdata('message', $count.' rows successfully added.');
session()->setFlashdata('alert-class', 'alert-success');
}
else{
session()->setFlashdata('message', 'CSV file coud not be imported.');
session()->setFlashdata('alert-class', 'alert-danger');
}
}else{
session()->setFlashdata('message', 'CSV file coud not be imported.');
session()->setFlashdata('alert-class', 'alert-danger');
}
}
return redirect()->route('/');
}
}After uploading the CSV file stores in the Codeigniter project local directory, thus, don’t forget to create the csvfile folder inside the public directory.
Set Up Routes
The controller class is ready, and now we need to execute the functions we defined in the controller class, Get into the Routes file, and create get and post method using $routes, get loads the view template. In contrast, the post invokes the request to store the CSV file data into the database.
Update app/Config/Routes.php file.
/*
* --------------------------------------------------------------------
* Route Definitions
* --------------------------------------------------------------------
*/
$routes->get('/', 'StudentController::index');
$routes->match(['get', 'post'], 'StudentController/importCsvToDb', 'StudentController::importCsvToDb')Create Codeigniter View File
In this section, we will work with the view file; this file will show the import CSV file data to the MySQL database component, therefore create index.php in the app/Views folder.
We need to design the form using Bootstrap 5, create a Codeigniter file upload form, tie the form with the post route, and informing the user about the file upload progress using the Bootstrap 5 alert box.
Update app/Views/index.php file.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Laravel Import Excel CSV to MySQL</title>
<meta name="description" content="The tiny framework with powerful features">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.1/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
.container {
max-width: 500px;
}
</style>
</head>
<body>
<div class="container mt-5">
<div class="card">
<div class="card-header text-center">
<strong>Upload CSV File</strong>
</div>
<div class="card-body">
<div class="mt-2">
<?php if (session()->has('message')){ ?>
<div class="alert <?=session()->getFlashdata('alert-class') ?>">
<?=session()->getFlashdata('message') ?>
</div>
<?php } ?>
<?php $validation = \Config\Services::validation(); ?>
</div>
<form action="<?php echo base_url('StudentController/importCsvToDb');?>" method="post" enctype="multipart/form-data">
<div class="form-group mb-3">
<div class="mb-3">
<input type="file" name="file" class="form-control" id="file">
</div>
</div>
<div class="d-grid">
<input type="submit" name="submit" value="Upload" class="btn btn-dark" />
</div>
</form>
</div>
</div>
</div>
</body>
</html>Start CI Application
The whole idea of this final section is about testing the feature we have built.
Thus, we need to go through a couple of steps, like starting the Codeigniter server, copying the given url, paste it into the browser and view the app.
php spark servehttp://localhost:8080
Conclusion
So, this was it; so far, we have described every bit of information to help you build a simple feature, import an excel CSV file to MySQL database in Codeigniter.
We tried our level best and left no stone unturned, and you can assimilate this profound guide; from now on, you will be able to replicate this feature in your CodeIgniter projects efficiently.
Working with data is the primary requirement; we don’t know in which or what format we receive data, that’s our job to place the data into the database pragmatically, and that’s exactly what we did in this Codeigniter 4 import CSV file data to MySQL database example.