+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Import CSV/Excel data into MYSQL database using codeigniter (/showthread.php?tid=69178)
Import CSV/Excel data into MYSQL database using codeigniter - Var91 - 10-16-2017
Hello Friends,
Today i came across a functionality where i need to import the CSV/Excel file in to MYSQL database via codeigniter. There is a special requirement from client where he can upload the CSV/Excel file in file upload field in HTML form and all data from CSV/Excel must import into MYSQL database table through PHP and remove the duplicates rows of data.But I faced some problems here.
My problems is:
1.when the user upload the csv file,its enter into the MYSQL database,but it have duplicates data of about 3-4 row.
2. the data in csv file is about 8000 rows but the data insert in MYSQL db is only about 5000(included the duplicates data)
3.this error occur when the file stop import the data
importcsv_error
this is my view:
Code:
<!-- UPDATE PROCESS'S OUTPUT -->
<div style="display: none;" id="modal3" class="modal hide in" tabindex="-1" role="dialog" aria-hidden="true">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
<h3 id="myModalLabel">Update Process's Output</h3>
</div>
<form action="{$path}pro/processupload" method="post" enctype="multipart/form-data" name="form1" id="form1">
<table>
<tr>
<td> Choose your file: </td>
<td><input type="file" class="form-control" name="userfile" id="userfile" align="center"/>
</td>
<td>
<div class="modal-footer">
<td colspan="2" ><input type="submit" id="submit" name="submit" value="Import"></td>
</div></td></tr></table>
</form>
</div><!-- end add product code-->PHP Code:
function processupload()
{
$this->load->model('upload_services');
$data['result']=$this->upload_services->upload_sampledata_csv();
$data['query']=$this-> upload_services->get_car_features_info();
$this->load->view(' Upload_csv ',$data);
}
and this is my model
PHP Code:
<?php
class Upload_services extends CI_Model
{
function __construct()
{
parent::__construct();
}
function upload_sampledata_csv()
{
if(isset($_POST['submit'])){
$fp = fopen($_FILES['userfile']['tmp_name'],'r') or die("can't open file");
while(($line = fgetcsv($fp)) !== FALSE)
{
//check whether there are duplicate rows of data in database
$prevQuery = array(
'articleno'=> $line[0] ,
'product_description' => $line[1] ,
'cust_name' => $line[2] ,
'size' => $line[3] ,
'colour' => $line[4],
'process_description' => $line[5],
'output' => $line[6],
'material_part' => $line[7],
'printingOutput' => $line[8]
);
$q=$this->db->select('sindi_productprocess_temp', $prevQuery)
->where('articleno',$line[0],
'product_description', $line[1] ,
'cust_name' , $line[2] ,
'size', $line[3] ,
'colour' , $line[4],
'process_description' , $line[5],
'output', $line[6],
'material_part', $line[7],
'printingOutput', $line[8]);
$prevResult = $this -> db->query($q);
if($prevResult->num_rows > 0){
//update process data
$data = array(
'articleno' => $line[0] ,
'product_description' => $line[1] ,
'cust_name' => $line[2] ,
'size' => $line[3] ,
'colour' => $line[4],
'process_description' => $line[5],
'output' => $line[6],
'material_part' => $line[7],
'printingOutput' => $line[8]
);
$this->db->set
(
'articleno',$line[0],
'product_description', $line[1] ,
'cust_name' , $line[2] ,
'size', $line[3] ,
'colour' , $line[4],
'process_description' , $line[5],
'output', $line[6],
'material_part', $line[7],
'printingOutput', $line[8]
);
$this->db-where
(
'articleno',$line[0],
'product_description', $line[1] ,
'cust_name' , $line[2] ,
'size', $line[3] ,
'colour' , $line[4],
'process_description' , $line[5],
'output', $line[6],
'material_part', $line[7],
'printingOutput', $line[8]
);
$this->db->update('sindi_productprocess_temp');
}else{
for($i = 0, $j = count($line); $i < $j; $i++)
{
$data = array(
'articleno' => $line[0] ,
'product_description' => $line[1] ,
'cust_name' => $line[2] ,
'size' => $line[3] ,
'colour' => $line[4],
'process_description' => $line[5],
'output' => $line[6],
'material_part' => $line[7],
'printingOutput' => $line[8]
);
$data['crane_features']=$this->db->insert('sindi_productprocess_temp', $data);
}
$i++;
}
}
fclose($fp) or die("can't close file");
}
}
function get_car_features_info()
{
$get_cardetails=$this->db->query("select * from sindi_productprocess_temp");
return $get_cardetails;
}
}
RE: Import CSV/Excel data into MYSQL database using codeigniter - Raymien - 10-17-2017
Hey there,
Have you tried using phpExcel? I've been using it on a small site (single upload at a time) and it seems to work just fine for xls files. I don't have a need for csv, so it may not be exactly what you need.
RE: Import CSV/Excel data into MYSQL database using codeigniter - dave friend - 10-17-2017
Is the value of the "articleno" column unique for each record? What I am trying to understand is what makes a row a "duplicate"?
RE: Import CSV/Excel data into MYSQL database using codeigniter - Var91 - 10-18-2017
Hi Raymien and dave friend ,
Thanks for taking time to reply,I have figure out how to solved this.
RE: Import CSV/Excel data into MYSQL database using codeigniter - nerdkeilan - 07-04-2018
(10-18-2017, 06:19 PM)Var91 Wrote: Hi Raymien and dave friend ,
Thanks for taking time to reply,I have figure out how to solved this.
Hello Var91,
How did you solve this problem ? I am having the same issue also. Kindly help out