In this tutorial, I will show you how to import CSV data into MySQL database in Codeigniter 4.
First, create a table in your MySQL database.
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Now, create a controller file named Users.php in the app/Controllers folder.
request->getFile(‘csv_file’);
$fileName = $file->getName();
$fileExtension = $file->getExtension();
$fileTempName = $file->getTempName();
if($fileExtension == ‘csv’){
$csvData = file_get_contents($fileTempName);
$rows = explode(“\n”, $csvData);
$header = str_getcsv(array_shift($rows));
$csv = array();
foreach ($rows as $row) {
$csv[] = array_combine($header, str_getcsv($row));
}
$db = Database::connect();
foreach($csv as $row){
$data = [
‘name’ => $row[‘name’],
’email’ => $row[’email’],
‘phone’ => $row[‘phone’]
];
$db->table(‘users’)->insert($data);
}
$data[‘success’] = ‘CSV data imported successfully.’;
echo view(‘users/import’, $data);
}else{
$data[‘error’] = ‘Please upload a valid CSV file.’;
echo view(‘users/import’, $data);
}
}
}
Now, create a view file named import.php in the app/Views/users folder.
Codeigniter 4 Import CSV Data to MySQL Database Example
Now, open the routes.php file and add the following routes.
$routes->get(‘users’, ‘Users::index’);
$routes->post(‘users/import’, ‘Users::import’);
Now, you can run the example.
http://localhost/codeigniter4/users
Sometimes, you need to import CSV or Excel file data from the database in Codeigniter 4 app. So, In this tutorial, you will learn how to import CSV or excel data to MySQL database using Codeigniter 4.
How to Import CSV File Data into MySQL Database in CodeIgniter 4
Let’s follow the following steps to import Excel data into MySQL database using CodeIgniter 4 apps:
- Step 1: Download Codeigniter 4 Project
- Step 2: Basic Configurations
- Step 3: Create a Table in the Database
- Step 4: Setup Database Credentials
- Step 5: Create a Controller
- Step 6: Create View
- Step 7: Create Route
- Step 8: Start Development Server
Step 1: Download 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
Next, you will set some basic configuration on the app/config/app.php file, so let’s go to application/config/config.php and open this file on text editor.
Set Base URL like this
public $baseURL = 'http://localhost:8080'; To public $baseURL = 'http://localhost/demo/';
Step 3: Create Table in Database
In this step, you need to create table in database and as well as insert some data to implement import csv or excel file in codeiginter 4. So visit your phpmyadmin panel and execute the following sql query in it:
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 Credentials
In this step, you need to connect our project to the database. you need to go app/Config/Database.php and open database.php file in text editor. After opening the file in a text editor, you need to set up database credentials in this file like below.
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 Controller
In this step, Visit app/Controllers and create a controller name Import.php. In this controller, you need to add the following methods into it:
<?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 View
In this step, you need to visit application/views directory. And create one view file that name home.php.
After that, add the following code into it:
<!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: Create Route
In this step, you need to create a route that renders the table into the view, place the following code in app/Config/Routes.php file.
$routes->get('/', 'Import::index');
Step 8: Start Development Server
In this step, open your terminal and execute the following command to start development sever:
php spark serve
Then, Go to the browser and hit below the URL:
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.
