i import an excel file to my database: here is my code to for reading excell and converting it into array of object
let excelVar = ref([])
;
let excelExport = (event) => {
var input = event.target;
var reader = new FileReader();
reader.onload = () => {
var fileData = reader.result;
var wb = XLSX.read(fileData, { type: "binary" });
wb.SheetNames.forEach((sheetName) => {
var rowObj = XLSX.utils.sheet_to_json(wb.Sheets[sheetName]);
excelVar.value = JSON.parse(JSON.stringify(rowObj));
excelBulkUpload(excelVar.value);
// window.location.reload();
});
};
reader.readAsBinaryString(input.files[0]);
$q.notify({
spinner:QSpinnerGears,
message: upload,
color: 'positive',
timeout:1000,
position:"right",
})
};
when i import excel data that was converted into an array of object into my database: the column that is supposed to be date is all 5 digit number.
my php code when inserting the converted data.
else if (array_key_exists('excelDatas', $arr )) {
// BULKINSERT
// check the size of array
$excel_rows = sizeof($arr['excelDatas']);
$check_existed_data = $this->db->rawQuery("SELECT COUNT(*)
FROM(
SELECT * FROM tbl_emp_information) AS derived");
// var_dump( $check_existed_data);
for ($x = 0; $x < $excel_rows ; $x++) {
$insert_data = $this->db->insert('tbl_emp_information', $arr['excelDatas'][$x]);
}
if($insert_data){
$query = $this->db->rawQuery("DELETE t1 FROM tbl_emp_information t1, tbl_emp_information t2
WHERE t1.employee_id > t2.employee_id AND t1.employee_ref_id = t2.employee_ref_id ");
$check_existed_updated = $this->db->rawQuery("SELECT COUNT(*)
FROM(
SELECT * FROM tbl_emp_information) AS derived");
// var_dump( $check_existed_data);
$existed = $check_existed_data[0]['COUNT(*)'];
$updated = $check_existed_updated[0]['COUNT(*)'];
$excel_rows = sizeof($arr['excelDatas']);
if($existed<$updated){
$result = $updated - $existed;
http_response_code(200);
echo $result . "/" . $excel_rows . " Record Saved";
}else{
echo "All records existed!";
return;
};
}
else{
echo json_encode(array('msg' => "Failed: " . $db->getLastError()));
}
return;
}