How to Import CSV File to MySQL Database in Codeigniter 4

Have you ever wondered how to import data from CSV files to the MySQL database in Codeigniter 4 app? Well, if your answer is no Fret Not, it will not remain the same forever.

Today, we will teach you by giving bit-by-bit information on uploading CSV file into the MySQL database from Codeigniter 4 platform.

Firstly, we would like to bring your attention to the CSV file, and We are sure you must have heard about the term CSV. But do you know what exactly it is?

The CSV stands for comma-separated values, and it is a delimited text file used for storing line-wise records with multiple values defined by comma-separated format.

Codeigniter 4 Import CSV File to MySQL Database Example Tutorial

  • Step 1: Install Codeigniter App
  • Step 2: Create New Table
  • Step 3: Update Database Details
  • Step 4: Switch On Error Handling
  • Step 5: Create CSV Document
  • Step 6: Setting Up Model File
  • Step 7: Organize Controller
  • Step 8: Register New Routes
  • Step 9: Build Codeigniter View
  • Step 10: View App in Browser

Install Codeigniter App

It would be better if you have Composer installed in your development machine, you can run given command to install the Codeigniter app.

Another, way to download the Codeigniter app is to directly download from the official site.

composer create-project codeigniter4/appstarter

After downloading and installing, unzip and change the project name as per your choice.

Create New Table

Up next, create a new table inside your database using the provided SQL command.

CREATE TABLE products (
    id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
    name varchar(100) NOT NULL COMMENT 'name',
    email varchar(100) NOT NULL COMMENT 'email',
    mobile varchar(100) NOT NULL COMMENT 'mobile',
    created_on varchar(40) NOT NULL COMMENT 'Created On',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Products data table' AUTO_INCREMENT=1;

Update Database Details

After creating the table into the DB, move to Config/Database.php and update your database details.

public $default = [
        'DSN'      => '',
        'hostname' => 'localhost',
        'username' => 'db_username',
        'password' => 'db_password',
        'database' => 'db_name',
        '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,
    ];

Switch On Error Handling

By default Codeigniter’s error debugging comes turned off, fret not! you can switch it on for the development purpose by going to app/Config/Boot/production.php and setting display_errors to 1.

ini_set('display_errors', '1');

Create CSV Document

To ease down you work, we have created a simple demo.csv document file. You can use this sample tiny CSV file for the demo purpose.

name, email, mobile, created_on
Elizabeth J Hostetler, alfreda1978@hotmail.com, 903-332-6744, 2010-05-11
Maria T Nave, immanuel.boy@gmail.com, 843-230-1388, 2012-02-04
Elvia C Nunez, alvena1971@gmail.com, 407-616-3700, 2015-05-05
Lucille M Saucedo, valentin.hamm@gmail.com, 323-414-1964, 2016-04-01
Billy J Jankowski, reva1992@gmail.com, 646-398-7344, 2018-06-12
Larry C Lincoln, kade1986@yahoo.com, 316-880-0100, 2020-01-10

Setting Up Model File

Model is useful for mapping the table properties, thus go inside the
app/Models/Product.php and define the given code within the file.

<?php 

namespace App\Models;

use CodeIgniter\Database\ConnectionInterface;
use CodeIgniter\Model;
 
class Product extends Model
{
    protected $table = 'products';

    protected $allowedFields = [
        'name', 
        'email', 
        'mobile',
        'created_on'
    ];
}

Organize Controller

The controller is the basic building block of CI app development; after all, all the logic goes into it.

After that, you have to create a new controller file and paste the provided code into the app/Controllers/ProductController.php.

<?php 

namespace App\Controllers;

use CodeIgniter\Controller;
use CodeIgniter\HTTP\RequestInterface;
use App\Models\Product;


class ProductController extends Controller
{
    public function index()
    {
        return view('product');
    }

    public function import()
    {
        $input = $this->validate([
            'file' => 'uploaded[file]|max_size[file,4098]|ext_in[file,csv]'
        ]);

        if (!$input) {
            $data['validation'] = $this->validator;
            return view('product', $data); 
        }else{

            if($file = $this->request->getFile('file')) {

            if ($file->isValid() && ! $file->hasMoved()) {
                $randomName = $file->getRandomName();

                $file->move('../public/csv/', $randomName);
                $file = fopen("../public/csv/".$randomName, "r");
                $i = 0;
                $fieldsNum = 4;

                $collection = array();
                
                while (($filedata = fgetcsv($file, 1500, ",")) !== FALSE) {
                    $num = count($filedata);
                    if($i > 0 && $num == $fieldsNum){ 
                        $collection[$i]['name'] = $filedata[0];
                        $collection[$i]['email'] = $filedata[1];
                        $collection[$i]['mobile'] = $filedata[2];
                        $collection[$i]['created_on'] = $filedata[3];

                    }
                    $i++;
                }
                fclose($file);

                $count = 0;
                foreach($collection as $prodData){
                    $product = new Product();

                    $getResults = $product->where('email', $prodData['email'])->countAllResults();

                    if($getResults == 0){
                        if($product->insert($prodData)){
                            $count++;
                        }
                    }
                }
                session()->setFlashdata('message', $count.' Item added to db.');
                session()->setFlashdata('alert-class', 'alert-info');
            } else{
                session()->setFlashdata('message', 'Error occured while importing CSV.');
                session()->setFlashdata('alert-class', 'alert-warning');
            }
            } else{
                session()->setFlashdata('message', 'Error occured while importing CSV.');
                session()->setFlashdata('alert-class', 'alert-warning');
            }
            return redirect()->route('/');
        }
    }
}

Register New Routes

Routes not just communicate with a controller but also executes the code defined into it, verily we need to perform a couple of tasks with the routes, so add the recommended code into app/Config/Routes.php file.

/*
 * --------------------------------------------------------------------
 * Route Definitions
 * --------------------------------------------------------------------
*/

$routes->get('/', 'ProductController::index');
$routes->post('/import-csv-to-database', 'ProductController::import');

Build Codeigniter View

We have just completed the 80% of our development task; in this final part, we have to create the view file and design the look and feel of the feature we have promised to build.

Insert the given code into the app/Views/product.php.

<!DOCTYPE html>
<html lang="en">

<head>
	<meta charset="UTF-8">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<title>Codeigniter 4 Upload CSV to Database Example</title>
	<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet">
</head>

<body>

	<div class="container">
		<div class="alert-block mt-3">
			<?php if (session()->has('message')){ ?>
			<div class="alert <?=session()->getFlashdata('alert-class') ?>">
				<?=session()->getFlashdata('message') ?>
			</div>
			<?php } ?>
			<?php $validation = \Config\Services::validation(); ?>
		</div>

		<form method="post" action="<?=base_url('import-csv-to-database') ?>" enctype="multipart/form-data">
			<div class="form-group mb-2">
				<input type="file" name="file" id="file" class="form-control">
			</div>
			<div class="form-group">
				<input type="submit" name="submit" value="Upload CSV" class="btn btn-primary" />
			</div>
		</form>
	</div>
</body>

</html>

View App in Browser

We are not just storing the CSV file data into the database but also saving the CSV file into the local project directory.

Consequently, create the public/csv folder, which will be the primary locus of CSV data files.

Now, we can proudly say we have completed this tutorial; just bother to run the given command to start the Codeigniter app and view the app in the browser.

php spark serve
http://localhost:8080

Import CSV to Database in Codeigniter

Summary

In this Codeigniter 4 import CSV to database example, we threw light on everything to understand the nuance of developing this functionality. We believe we will be profoundly helpful in the pursuit of enhancing our skills in Codeigniter development.