Import CSV to MySQL Database in Codeigniter
If you are searching for an Import CSV file to MySQL DataBase in your Codeigniter project, so your searching ends here. In this tutorial, I would like to share with you how to Import CSV files to MySQL Database very easily in your favorite PHP framework. I will show you step by step example of an Import CSV.
Download Codeigniter
If you did not download Codeigniter, click the below link to download Codeigniter and set it up in your localhost. If it has been done, leave that. Download Codeigniter
Connect Database
Create a database in phpMyadmin. Open database.php file from this path application\config and set up database record.
$db['default'] = array( 'dsn' => '', 'hostname' => 'localhost', 'username' => 'root', 'password' => '', 'database' => 'codeigniter_demo', 'dbdriver' => 'mysqli', 'dbprefix' => '', 'pconnect' => FALSE, 'db_debug' => (ENVIRONMENT !== 'production'), 'cache_on' => FALSE, 'cachedir' => '', 'char_set' => 'utf8', 'dbcollat' => 'utf8_general_ci', 'swap_pre' => '', 'encrypt' => FALSE, 'compress' => FALSE, 'stricton' => FALSE, 'failover' => array(), 'save_queries' => TRUE );
Create a Customer table.
create a table where you place your CSV records, here I have created a table customer, copy the below code.
CREATE TABLE `customer` ( `id` int(11) NOT NULL, `custname` varchar(100) DEFAULT NULL, `custaddress` text DEFAULT NULL, `country` varchar(20) DEFAULT NULL, `state` varchar(30) DEFAULT NULL, `city` varchar(20) DEFAULT NULL, `emailid` varchar(50) DEFAULT NULL, `status` int(1) DEFAULT NULL COMMENT '1=Active, 0=Inactive, 2=Remove', `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `customer` ADD PRIMARY KEY (`id`); ALTER TABLE `customer` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; COMMIT;
Set Config:
Open config.php file from this path application\config add set the project URL and make blank index_page. How to create .htaccess file – Htaccess file for your Project
$config['base_url'] = 'http://192.168.0.110/codeigniter_demo/'; $config['index_page'] = '';
Create .htaccess
Create a .htaccess file in your Codeigniter project and copy-paste the below code.
Path: yourProject/.htaccess
RewriteEngine On RewriteCond %{REQUEST_FILENAME} !-f RewriteCond %{REQUEST_FILENAME} !-d RewriteRule ^(.*)$ index.php/$1 [L]
Route
I have set a default route for displaying my CSV upload form. After that, I have created a controller file and give the name HomeController.
$route['default_controller'] = 'HomeController'; $route['404_override'] = ''; $route['translate_uri_dashes'] = FALSE; $route['upload-csv'] = 'HomeController/upload_csv';
Create a view page
create a view page under the view folder.
Path: view/csv_form.php
<!DOCTYPE html> <html lang="en"> <head> <title>Example</title> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script> </head> <body> <div class="container"> <h2>Upload CSV</h2> <form action="<?php echo base_url('upload-csv')?>" method="post"> <div class="form-group"> <label for="email">CSV:</label> <input type="file" class="form-control" name="customer_csv_file" id="customer_csv_file"> </div> <button type="submit" class="btn btn-default">Submit</button> </form> </div> </body> </html>
Controller:
Copy the controller code to and past this code in HomeController. It will display your CSV form page.
<?php defined('BASEPATH') OR exit('No direct script access allowed'); class HomeController extends CI_Controller { function __construct() { parent::__construct(); $this->load->helper('URL'); } public function index() { $this->load->view('csv_form'); } } ?>
CSV Upload Code:
Apply these codes to Import CSV to MySQL DataBase.
<?php defined('BASEPATH') OR exit('No direct script access allowed'); class HomeController extends CI_Controller { function __construct() { parent::__construct(); $this->load->helper('URL'); $this->load->database(); } public function index() { $this->load->view('csv_form'); } public function upload_csv() { $config['upload_path'] = FCPATH .'csv_files/'; $config['allowed_types'] = 'csv';//'gif|jpg|png'; $config['max_size'] = 128000; $config['max_width'] = 1024; $config['max_height'] = 768; /* 'prodfamily'=>$this->db->select('prodfamilycode as value')->order_by('prodfamily')->get('prodfamily')->result_array() */ $this->load->library('upload', $config); if(!$this->upload->do_upload('customer_csv_file')) { //echo "here"; exit(); $dt["error"] = array('error' => $this->upload->display_errors()); }else { //echo "here2"; exit(); $data = array('upload_data' => $this->upload->data()); $dt = $this->decode_csv($data["upload_data"]["full_path"],','); foreach($dt['data'] as $k=>$v) { $CustomerArray = array('custname' => $dt['data'][$k]['custname'], 'custaddress' => $dt['data'][$k]['custaddress'], 'country' => $dt['data'][$k]['country'], 'state' => $dt['data'][$k]['state'], 'city' => $dt['data'][$k]['city'], 'emailid' => $dt['data'][$k]['emailid'], 'status' => 1, 'created_at' => date('Y-m-d H:i:s'), 'updated_at' => date('Y-m-d H:i:s')); $qry = $this->db->insert('customer', $CustomerArray); if($qry) { echo "success"; }else { echo "error"; } } } } public function decode_csv($csv, $delimiter=";") { if(!file_exists($csv) || !is_readable($csv)) return array("data"=>array(), "error"=>"no file"); $header = NULL; $data = array(); if (($handle = fopen($csv, 'r')) !== FALSE) { while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE) { if(!$header) $header = $row; else if(count($header) ==count( $row)) $data[] = array_combine($header, $row); } fclose($handle); } return array("data"=>$data, "error"=>""); } } ?>
Hope it will be helpful for you.