php - CSV import in Yii2 -
did?
i have trying upload csv file in project module.the csv format given below.at time of upload getting error message "1 row has validation error". values not inserted in db.
what want?
i want upload csv file data in mysql db.
**my csv format** ============================================================================== model_name|imei_no|promoter_vendor_id|device_report_id|allocation_date ============================================================================== sam | 2342 | 7 | 3 |2015 ============================================================================== insert query mysql
insert `dbname`.`tbl_device` (`device_id`,`model_name`,`imei_no`,`promoter_vendor_id`,`device_report_id`, `allocation_date`,`inserted_date`,`inserted_ip`,`inserted_by`,`updated_date`, `updated_ip`,`updated_by`,`status_in`,`record_status`)values (auto_inc,sam,34234,7,3,2015,?,?,?,?,?,?,?,?); model import function
in model trying create insert query in yii model. function device_insert_by_csvfile() { /* table name*/ $table='tbl_device'; $row = 1; $field=''; $value=''; $success=0; $error=0; //check file available or not if(file_exists('device_csv.csv')){ $handle = fopen('device_csv.csv', "r"); $valuecoma=''; $model_name=$imei_no=$promoter_vendor_id=$device_report_id=$allocation_date=-1; while (($data = fgetcsv($handle, 1000, ",")) !== false) { $num = count($data);echo "<script>alert(".$num.");</script>"; // skip validate error row if( $row !=1 && (trim($data[$model_name])=='' || trim($data[$imei_no])=='' || trim($data[$promoter_vendor_id])=='' || trim($data[$device_report_id]) || trim($data[$allocation_date]) =='')){ $error++; continue; } // table fields binding if($row ==1){ $coma=''; ($c=0; $c < $num; $c++) { if(trim($data[$c])=='model_name'){ $model_name=$c; } if(trim($data[$c])=='imei_no'){ $imei_no=$c; } if(trim($data[$c])=='promoter_vendor_id'){ $promoter_vendor_id=$c; } if(trim($data[$c])=='device_report_id'){ $device_report_id=$c; } if(trim($data[$c])=='allocation_date'){ $allocation_date=$c; } $field.= $coma.trim($data[$c]); $coma=','; } } //values binding else{ // inserted row count $success++; $totid++; // insert record value binding $value.=$valuecoma."("; $vcoma=''; ($c=0; $c < $num; $c++) { if($promoter_vendor_id ==$c && trim($data[$c]) !=''){ $ftype=usertype::find()->where("id='$data[$c]' or type like'%$data[$c]%'")->asarray()->one(); if(count($ftype) >0){ $value.=$vcoma."'".$ftype['id']."'" ; $vcoma=','; }else{ $value.=$vcoma."'0'" ; $vcoma=','; } }else{ $value.=$vcoma."'".addslashes(trim($data[$c]))."'" ; $vcoma=','; } } $value.=")"; $valuecoma=','; } $row++; } fclose($handle); unlink('device_csv.csv'); if($value !=''){ $sql ="insert $table ($field) values $value"; $connection = \yii::$app->db; $command=$connection->createcommand($sql); $datareader=$command->execute(); } } return array($success,$error); } my view page
<!-- device import , export --> <div class="tab-pane" id="device"> <br/> <?php if(!empty($deverror)){?> <div class="alert alert-danger"><?=$deverror?> <?=yii::t('app', $deverror=='1'?'row has':'rows have')?> <?=yii::t('app', 'validation error')?></div> <?php } ?> <?php if(!empty($devmsg)){?> <div class="alert alert-success"><?=$devmsg?> <?=yii::t('app', $devmsg=='1'?'row has':'rows have')?> <?=yii::t('app', 'been inserted')?></div> <?php } ?> <form action="" id="device_frm" method="post" enctype="multipart/form-data" style="display:inline"> <?php yii::$app->request->enablecsrfvalidation = true; ?> <input type="hidden" name="_csrf" value="<?php echo $this->renderdynamic('return yii::$app->request->csrftoken;'); ?>"> <div class="form-group"> <i><?=yii::t('app', 'note: file type should "csv"')?></i> ( <i style="color:#f00; font-size:12px"><?=yii::t('app', 'required fields*: model_name,imei_no,promoter_id,device_report(mins)')?></i> ) <br><br> <label><?=yii::t('app', 'csv file')?></label> <input type="file" name="device_csv_file" class="form-control" id="device_csv_file" data-validation="required"> </div> <?= html::submitbutton(yii::t('app', 'device csv'), ['class' => 'btn btn-sm btn-primary device_submit']) ?> </form> <?php if(isset($_files['device_csv_file'])){?> <form action="" method="post" enctype="multipart/form-data" style="display:inline"> <input type="hidden" name="_csrf" value="<?php echo $this->renderdynamic('return yii::$app->request->csrftoken;'); ?>"> <input type="hidden" name="device_csv_upload" value="true"> <?= html::submitbutton(yii::t('app', 'import file'), ['class' => 'btn btn-sm btn-primary customer_csv_upload']) ?> </form> <?php } ?> <a class="btn btn-success btn-sm" target="_blank" href="../../livefactory/csv_files/device_template.csv" data-original-title="" title=""> <span class="fa fa-file-excel-o"></span> <?=yii::t('app', 'download device csv import template')?> </a> </div>
according mysql query post, there missing quotes around sam
insert `dbname`.`tbl_device` (`device_id`, `model_name`, `imei_no`, `promoter_vendor_id`, `device_report_id`, `allocation_date`, `inserted_date`, `inserted_ip`, `inserted_by`, `updated_date`, `updated_ip`, `updated_by`, `status_in`,`record_status`) values (auto_inc, 'sam', 34234, 7,3,2015,?,?,?,?,?,?,?,?); during parsing of csv line, should create array $string mark $string[0]=true; (0 index of model_name column) add quotes in second loop if $string[$c] === true. don't copy / paste solution there lot of code here change 2 ou 3 lines.
hope helps.
Comments
Post a Comment