Importing bulk data through excel sheet .csv (comma delimited) is one of the useful function to reduce the one by one data updating. and sure that excel sheet format should be .csv (comma delimited) format, while saving excel sheet, save it in .csv (comma delimited) format. and lets see the code for that.
DATABASE DETAILS
DATABASE NAME : 2my4edge (in download file 'test')
TABLE NAME : import
CREATE TABLE `import` ( `id` int(11) NOT NULL, `name` varchar(250) NOT NULL, `email` varchar(250) NOT NULL, `created_date` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SAMPLE EXCEL SHEET FORMAT
Then save that Excel sheet in .csv (Comma delimited) format. Column A is for 'name' and B is for 'email'.
CONTROLLER FILE
Uploadcsv.php
<?php defined('BASEPATH') OR exit('No direct script access allowed'); class Uploadcsv extends CI_Controller { public function __construct() { parent::__construct(); $this->load->helper('url'); $this->load->model('Welcome_model','welcome'); } public function index() { $this->data['view_data']= $this->welcome->view_data(); $this->load->view('excelimport', $this->data, FALSE); } public function importbulkemail(){ $this->load->view('excelimport'); } public function import(){ if(isset($_POST["import"])) { $filename=$_FILES["file"]["tmp_name"]; if($_FILES["file"]["size"] > 0) { $file = fopen($filename, "r"); while (($importdata = fgetcsv($file, 10000, ",")) !== FALSE) { $data = array( 'name' => $importdata[0], 'email' =>$importdata[1], 'created_date' => date('Y-m-d'), ); $insert = $this->welcome->insertCSV($data); } fclose($file); $this->session->set_flashdata('message', 'Data are imported successfully..'); redirect('uploadcsv/index'); }else{ $this->session->set_flashdata('message', 'Something went wrong..'); redirect('uploadcsv/index'); } } } }
VIEW FILE
excelimport.php
<?php if($this->session->flashdata('message')){?> <div align="center" class="alert alert-success"> <?php echo $this->session->flashdata('message')?> </div> <?php } ?> <br><br> <div align="center"> <form action="<?php echo base_url(); ?>index.php/uploadcsv/import" method="post" name="upload_excel" enctype="multipart/form-data"> <input type="file" name="file" id="file"> <button type="submit" id="submit" name="import">Import</button> </form> <br> <br> <a href="<?php echo base_url(); ?>sample.csv"> Sample csv file </a> <br><br> <div style="width:80%; margin:0 auto;" align="center"> <table id="t01"> <tr> <th>Name</th> <th>Email</th> <th>Date</th> </tr> <?php if(isset($view_data) && is_array($view_data) && count($view_data)): $i=1; foreach ($view_data as $key => $data) { ?> <tr> <td><?php echo $data['name'] ?></td> <td><?php echo $data['email'] ?></td> <td><?php echo $data['created_date'] ?></td> </tr> <?php } endif; ?> </table> </div> </div>
MODEL FILE
Welcome_model.php
<?php class Welcome_model extends CI_Model { public function __construct() { parent::__construct(); } public function insertCSV($data) { $this->db->insert('import', $data); return TRUE; } public function view_data(){ $query=$this->db->query("SELECT im.* FROM import im ORDER BY im.id DESC limit 10"); return $query->result_array(); } }
That's it. it will import the .csv file to your mysql database. i hope you like it. If you have any Queries comment below.
RELATED POSTS :
I like the way you helped me this time!! thank you very Much
ReplyDeletethankyou , you help me , thank you
ReplyDeletesimilarly i want to download a csv template from server and fill then upload. How to do it?
ReplyDeletethank you it's working
ReplyDeletefunction importcsv() {
ReplyDelete$res = $this->getAllLocation();
ini_set('auto_detect_line_endings', true);
if ($_FILES ['csv_name'] != '') {
$adminId=$this->session->userdata('admin_id');
//$language=$this->input->post('selLanguage');
//setSAActivityLogs('Transaction_activity','SAKeywordscv',"upload By :- ".$adminId);
$error = array();
$line_error = 1;
$base_url = base_url();
$file_name = time() . $_FILES ["csv_name"] ["name"];
move_uploaded_file($_FILES ["csv_name"] ["tmp_name"], "./uploads/keywordcsv/" . $file_name);
$upfile = "./uploads/keywordcsv/$file_name";
$fp = fopen($upfile, "r");
$n = 0;
$k = 0;
$duplicate = array();
$flag = 0;
while ($line1 = fgets($fp)) {
$importData = str_getcsv($line1, ',', '"');
if ($importData [0] != '') {
if ($n > 0) {
$txtid = $importData[0];
$location_name = $importData[1];
//echo $location_name;exit;
for ($i = 0; $i < count($res); $i++)
{
$res = $this->getAllLocation();
if ($res[$i]->location_name == $location_name) {
$duplicate[$k] = $location_name;
$k++;
$flag = 1;
}
}
if ($flag == 0)
{
$data = array('county_id' => $txtid,
'location_name' => $location_name,
'status' => 1);
// print_r($data);exit;
$this->db->insert('data_location', $data);
}
$flag = 0;
}
$n ++;
}
}
$this->create_log(" Location Csv", "Location","upload By :- ".$adminId);
$this->session->set_userdata('duplicate', $duplicate);
//$str=implode(" ", $duplicate);
return true;
}
return false;
}
chutiye .................
Deletehi
ReplyDeletehow can i set get data from column 2?
ReplyDeletethe code is not working .garbage values are inserting to the table
ReplyDeleteNext type the path and filename where you want the merged file to appear. Hit enter, and windows will create a combined file! Easy as that.excel reporting dashboard
ReplyDeleteNice job dude.
ReplyDeleteIts work perfectely without any error..
my excel import code is working in local but in online my code is not working
ReplyDeletedisplays page not found 404 error
kindly clarify
Great blog. You put Good stuff. All the topics were explained briefly. So quickly understand for me. I am waiting for your next fantastic blog. Thanks for sharing. Any course related details learn...
ReplyDeletePhp course in chennai
my excel code is working in my localhost but when i upload it to my server it shows 404 error found and the page not found. I checked from my end. Kindly help me to sort it out.
ReplyDeleteA PHP Error was encountered
ReplyDeleteSeverity: Notice
Message: Undefined property: Uploadcsv::$db
Filename: core/Model.php
Line Number: 52
Fatal error: Call to a member function query() on a non-object in C:\xampp3\htdocs\exceli\application\models\Welcome_model.php on line 18
Any terrible string or procedure can cause such click here a fitting activity easily, even SQLite library can't help you in this circumstance.
ReplyDelete