Import csv data in bulk via a CSV file is one of the essential features of any web application and will decrease the time needed to insert data one by one. Here we want to import data from the CSV file into the Mysql database in the Codeigniter framework. There is no library for a CSV file in Codeigniter, but we’ve checked the internet and we’ve found a library named ‘csvimport’ which is built to import CSV data into the Codeigniter application. So we’ve used the ‘csvimport’ library to import data from the CSV file to the Mysql database in the Codeigniter Application.
When you import bulk data from a CSV file and use the Codeigniter framework for web app development, you can use the Codeigniter Database library insert_batch() method. By using this approach in single query execution we can execute multiple Insert data queries. So, by using this method, we can import a large amount of data in a very short time, since by using the ‘csvimport’ library, we have an array of CSV file data, and then we use insert_batch() to directly import all data into a single query. So this is one of the benefits of the Codeigniter framework if you import large data.
In the article code, we will use the CodeIgniter ‘csvimport’ library to import user data from CSV file into the database. To explain the functionality of CodeIgniter CSV Import the following process will be implemented.
- Fetch all the user’s data from the database and listed on the web page.
- CSV file upload form.
- Parse and import CSV data in the database.
Take a look at the files structure before you start implementing the import CSV data into the database in the CodeIgniter framework.
codeigniter_import_csv/
|
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
codeigniter_csv_import/ ├── uploads/ ├── application/ ├── config/ │ │ └── autoload.php │ ├── controllers/ │ │ └── Users.php │ ├── libraries/ │ │ └── csvimport.php │ ├── models/ │ │ └── User.php │ └── views/ │ └── users/ │ └── index.php └── assets/ ├── css/ └── js/ |
Step-1: CSV File Format
The CSV file will have 4 fields-Name, Email, Phone, Status, based on the structure of its database table.
Step-2: Create Database Table
Copy and Run the following MySql Database query to create the “users” table to store CSV file data.
|
2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL, `created` datetime NOT NULL, `modified` datetime NOT NULL, `status` enum('Active','Inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
Step-3: config/autoload.php
Define the commonly used library and helper in the config/autoload.php file to load automatically on every request.
|
2 3 4 5 |
$autoload['libraries'] = array('database', 'session'); $autoload['helper'] = array('url'); |
Step-4: libraries/csvimport.php
The library allows you to read a CSV file in CodeIgniter application and convert CSV data in an array. You can import data from the CodeIgniter CSV file by using this “csvimport” class.
|
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); /** * CodeIgniter CSV Import Class * * This library will help import a CSV file into * an associative array. * * This library treats the first row of a CSV file * as a column header row. * * * @package CodeIgniter * @subpackage Libraries * @category Libraries * @author Brad Stinson */ class Csvimport { var $fields;/** columns names retrieved after parsing */ var $separator = ';';/** separator used to explode each line */ var $enclosure = '"';/** enclosure used to decorate each field */ var $max_row_size = 120400;/** maximum row size to be used for decoding */ function parse_file($p_Filepath) { $file = fopen($p_Filepath, 'r'); $this->fields = fgetcsv($file, $this->max_row_size, $this->separator, $this->enclosure); $keys_values = explode(',', $this->fields[0]); $content = array(); $keys = $this->escape_string($keys_values); $i = 1; while (($row = fgetcsv($file, $this->max_row_size, $this->separator, $this->enclosure)) != false) { if ($row != null) { // skip empty lines $values = explode(',', $row[0]); if (count($keys) == count($values)) { $arr = array(); $new_values = array(); $new_values = $this->escape_string($values); for ($j = 0; $j < count($keys); $j++) { if ($keys[$j] != "") { $arr[$keys[$j]] = $new_values[$j]; } } $content[$i] = $arr; $i++; } } } fclose($file); return $content; } function escape_string($data) { $result = array(); foreach ($data as $row) { $result[] = str_replace('"', '', $row); } return $result; } } |
Step-5: controllers/Users.php
The Users controller handles the CSV data import process.
__construct() – Loads the required library (csvimport), helper (file), and model (user).
index() – List the users data.
import() – Import CSV or Excel file data to the database.
|
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
<?php class Users extends CI_Controller { public function __construct() { parent::__construct(); $this->load->model('user'); $this->load->library('csvimport'); $this->load->helper('file'); } public function index(){ $data = array(); // Get rows $data['users'] = $this->user->getAllUsers(); // Load the list page view $this->load->view('users/index', $data); } public function import() { $path = FCPATH . "uploads/"; $config['upload_path'] = $path; $config['allowed_types'] = 'csv'; $config['max_size'] = 1024000; $this->load->library('upload', $config); $this->upload->initialize($config); if (!$this->upload->do_upload('file')) { $error = $this->upload->display_errors(); $this->session->set_flashdata('error', $this->upload->display_errors()); redirect("users"); //echo $error['error']; } else { $file_data = $this->upload->data(); $file_path = base_url() . "uploads/" . $file_data['file_name']; $csv_data = $this->mycsv->parse_file($file_path); // Add created and modified date if not include $date = date("Y-m-d H:i:s"); if ($csv_data) { foreach ($csv_data as $row) { $insert_data[] = array( 'name' => $row['Name'], 'email' => $row['Email'], 'phone' => $row['Phone'], 'status' => $row['Status'], 'created' => $date, 'modified' => $date, ); } $this->user->insert_user($insert_data); $this->session->set_flashdata('success', "Csv imported successfully"); redirect("users"); } else { $data['error'] = "Error occured"; $this->session->set_flashdata('error', $data['error']); redirect("users"); } } } } |
Step-6: models/User.php
The User model handles the database related works (Fetch and Insert).
|
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<?php class User extends CI_Model { public function __construct() { $this->load->database(); } /* * Insert users data into the database * @param $data data to be insert based on the passed parameters */ function insert_user($data) { $this->db->insert_batch('users', $data); } function getAllUsers() { $result = $this->db->get('users'); return $result->result_array(); } |
Step-7: View/users/index.php
Here, all the existing user’s data is fetched from the database and listed on the webpage.
|
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
<!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>CodeIgniter CSV Import</title> <!-- Bootstrap library --> <link rel="stylesheet" href="<?php echo base_url('assets/css/bootstrap.min.css'); ?>"> <!-- Stylesheet file --> <link rel="stylesheet" href="<?php echo base_url('assets/css/style.css'); ?>"> </head> <body> <div class="container"> <h2>Users List</h2> <!-- Display status message --> <?php if ($this->session->flashdata('success')) { ?> <div class="alert alert-success"> <a href="#" class="close" data-dismiss="alert">×</a> <strong>Success!</strong> <?php echo $this->session->flashdata('success'); ?> </div> <?php } elseif ($this->session->flashdata('error')) { ?> <div class="alert alert-danger"> <a href="#" class="close" data-dismiss="alert">×</a> <strong>Error!</strong> <?php echo $this->session->flashdata('error'); ?> </div> <?php } ?> <div class="row"> <!-- File upload form --> <div class="col-md-12" id="importFrm"> <form action="<?php echo base_url('users/import'); ?>" method="post" enctype="multipart/form-data"> <input type="file" name="file" /> <input type="submit" class="btn btn-primary" name="importSubmit" value="IMPORT"> </form> </div> <!-- Data list table --> <table class="table table-striped table-bordered"> <thead class="thead-dark"> <tr> <th>#ID</th> <th>Name</th> <th>Email</th> <th>Phone</th> <th>Status</th> </tr> </thead> <tbody> <?php if(!empty($users)){ foreach($users as $row){ ?> <tr> <td><?php echo $row['id']; ?></td> <td><?php echo $row['name']; ?></td> <td><?php echo $row['email']; ?></td> <td><?php echo $row['phone']; ?></td> <td><?php echo $row['status']; ?></td> </tr> <?php } }else{ ?> <tr><td colspan="5">No member(s) found...</td></tr> <?php } ?> </tbody> </table> </div> </div> </body> </html> |
ALSO READ: How to Import MySQL Database from SQL File using PHP
Conclusion:
We know how to import CSV data into the Mysql table using PHP script if you don’t know then follow the above link. Now in this post, we have discussed this topic into Codeigniter Framework.
Are you want to get implementation help, or modify or extend the functionality of this script? Submit a paid service request
Pradeep Maurya is the Professional Web Developer & Designer and the Founder of “Tutorials website”. He lives in Delhi and loves to be a self-dependent person. As an owner, he is trying his best to improve this platform day by day. His passion, dedication and quick decision making ability to stand apart from others. He’s an avid blogger and writes on the publications like Dzone, e27.co
