0

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. enter image description here

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;


        }
  • What are those number suppose to be? For example: `44781` is? (example `YYYY-MM-DD`), `44824` is? – vee Sep 22 '22 at 03:52
  • Read more about this [here](https://stackoverflow.com/questions/32624838/read-date-format-in-php-excel) (PHP), or [here](https://stackoverflow.com/questions/53163552/format-date-with-sheetjs) (JS). – vee Sep 22 '22 at 03:55
  • @vee yes its date like you mention: YYYY-MM-DD – Michael Angelo Corral Sep 22 '22 at 04:03
  • No, I means what **number** of date suppose to be? Not `YYYY-MM-DD` that is just sample format. `44781` = **what?** 2022-09-22? 1999-12-25? or what? – vee Sep 22 '22 at 09:30
  • However, please follow links in my another comment. – vee Sep 22 '22 at 09:31

0 Answers0