Codeigniter 4 Import Data to Excel/CSV File From MySQL Database

torque-programming 0 Comments 6 min read

In this tutorial, you’ll learn how to bring data from an Excel or CSV file into a MySQL database using CodeIgniter 4. It can be helpful to import data from files like this when working on a CodeIgniter 4 app. We’ll go through the process step by step, so you can learn how to import dynamic data in Excel or CSV format into your CodeIgniter 4 database.

How to Import Data from Excel or CSV to mysql Database using Codeigniter 4

Let’s follow the following steps to import excel data into MySQL database using CodeIgniter 4 apps:

Download Codeigniter 4 Project
Basic Configurations
Create Table in Database
Setup Database Credentials
Create Controller
Create View
Create Route
Start Development Server

Download Codeigniter 4 Project

Now, we’ll move on to the next step. You’ll need to download the latest version of CodeIgniter 4, which you can do by visiting this link: https://codeigniter.com/download. Once you’re there, just click on the download button and wait for it to finish. Then, unzip the setup file on your local system in the xampp/htdocs/ folder. Make sure to rename the folder you downloaded as “demo”. That’s it for this step!

Basic Configurations

Now, we need to configure some basic settings in the “app/config/app.php” file. To do this, we will open the “config.php” file located in the “application/config” folder using a text editor.

Set Base Url Like This

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

Create Table in Database

Now, we need to create a database for our CodeIgniter 4 application. To do this, we will use PHPMyAdmin, which is a popular web-based database management tool.

To get started, open PHPMyAdmin and create a new database with the name “demo”.

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;

Setup Database Credentials

Now, we need to connect our CodeIgniter 4 project to the database. To do this, we will open the “database.php” file located in the “app/Config” folder using a text editor.

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,
];

Create Controller

For the next step, go to the “app/Controllers” folder and create a new controller called “Import.php”. Once you’ve created it, add the following methods to 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('/'); 
	}
}

Create View

To do this step, go to the “application/views” folder. Create a new file there called “home.php”. Then, copy and paste the following code into the file:

<!DOCTYPE html>
<html>
    <head>
        <title>Codeigniter 4 Import Excel or CSV File into Database Example - torqueprogramming.co.in</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>

Create Route

To complete this step, you will need to create a route that shows the table on the screen. You can do this by opening the “app/Config/Routes.php” file and adding the following code.

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

Start Development Server

To begin this step, open up your terminal or command prompt. Then, type in the following command to start the development server:

php spark serve

Hit this link

http://localhost:8080

Conclusion

This tutorial teaches you how to bring data from an Excel or CSV file into your MySQL database using CodeIgniter 4. You now know how to import CSV or Excel data into your CodeIgniter 4 app’s MySQL database.

If you have any questions or comments, please feel free to use the comment section below to reach out to us.

You May Also Like