import excel to mysql Error

Question

I am trying to import data from an Excel file to Mysql, but it is not inserting the data

Controller

public function guardar_horario(){


 if (!empty($_FILES['file']['name'])) {


 $pathinfo = pathinfo($_FILES["file"]["name"]);


  if (($pathinfo['extension'] == 'xlsx' || $pathinfo['extension'] == 'xls') 
       && $_FILES['file']['size'] > 0 ) {

    // Temporary file name
    $inputFileName = $_FILES['file']['tmp_name']; 

    // Read excel file by using ReadFactory object.
    $reader = ReaderFactory::create(Type::XLSX);

    // Open file
    $reader->open($inputFileName);
    $count = 1;

    // Number of sheet in excel file
    foreach ($reader->getSheetIterator() as $sheet) {

        // Number of Rows in Excel sheet
        foreach ($sheet->getRowIterator() as $row) {

            // It reads data after header. In the my excel sheet, 
            // header is in the first row. 
            if ($count > 1) { 

                // Data of excel sheet
                $data['hrs_ini'] = $row[0];
                $data['hrs_ter'] = $row[1];
                $data['lunes'] = $row[2];
                $data['martes'] = $row[3];
                $data['miercoles'] = $row[4];
                $data['jueves'] = $row[5];
                $data['viernes'] = $row[6];
                $data['sabado'] = $row[7];



           $this->db->insert('horario',$row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6],$row[7]);


           //$sql="INSERT INTO estudiantes (nombre_es, apellido_pa, apellido_ma) VALUES('$row[0]','$row[1]','$row[2]')";
           //$resultado=mysqli_query($conn,$sql);


                //print_r($data);

            }
            $count++;
        }
     }

     // Close excel file
     $reader->close();

    } else {

    echo "Please Select Valid Excel File";
  }

 } else {

 echo "Please Select Excel File";

 }
}
}

The error is

A Database Error Occurred

You must use the "set" method to update an entry.

Filename: C:/xampp/htdocs/SAE/system/database/DB_query_builder.php

Line Number: 1686

I think the syntax is wrong, I am new to codeigniter, and I am from Chile, I am sorry if it is not understood very well, but most of what I have found, has been in English, for them I ask the English speaking community.

Well, can you show the UPDATE part of the code? Can't really say anything without it. — Qirel, Sep 15 '17 at 14:19
Ah, I see. Have a look at this thread: https://stackoverflow.com/questions/30379100/error-you-must-use-the-set-method-to-update-an-entry-fix — Qirel, Sep 15 '17 at 14:23

Brian Gottier · Answer 1 · 2017-09-15T14:58:24.273

You need to wrap your inserted params in an array, or at least that is one mistake:

$this->db->insert(
    'horario',
    array($row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6],$row[7])
);

I think technically you would want to add your column names too, so you might have to do something like this:

$this->db->insert(
    'horario',
    array(
        'hrs_ini'   => $row[0],
        'hrs_ter'   => $row[1],
        'lunes'     => $row[2],
        'martes'    => $row[3],
        'miercoles' => $row[4],
        'jueves'    => $row[5],
        'viernes'   => $row[6],
        'sabado'    => $row[7]
    )
);

But depending on your data, this might be all you need:

$this->db->insert( 'horario', $data );

score 0 · Accepted Answer · answered Sep 16 '17 at 13:21

0

Your syntax is wrong. Please try this code and make sure in the array the column name should be same. For ex. 'hrs_ini' so in the db table the column name must be same as 'hrs_ini' otherwise data will not insert if any one of them is different or not found. Let me know is it working or not.

if($count > 1) { 
    //Make array here
    $data = array(
        'hrs_ini' => $row[0],
        'hrs_ter' => $row[1],
        'lunes' => $row[2],
        'martes' => $row[3],
        'miercoles' => $row[4],
        'jueves' => $row[5],
        'viernes' => $row[6],
        'sabado' => $row[7]
    ); 
    //Insert the data here
    $this->db->insert('horario',$data);
}

answered Sep 16 '17 at 13:21

Anand Pandey

2,025
3
20
39

It works. is saving the data, thanks for your help, however the first two columns are not inserted, in the EXCEL file are dates. Any idea why? Thanks again – Kvothe_0077 Sep 17 '17 at 03:51
Is your first two column is 'datetime''in mysql? – Anand Pandey Sep 18 '17 at 05:10
Is "Time" no "Datetime" – Kvothe_0077 Sep 18 '17 at 13:22
Try like this in array 'hrs_ini' => date('H:i', strtotime($row[0])), 'hrs_ter' => date('H:i', strtotime($row[1])), – Anand Pandey Sep 18 '17 at 13:35
OR if you are using timestamp than follow this page https://stackoverflow.com/questions/2501915/convert-date-string-to-mysql-datetime-field – Anand Pandey Sep 18 '17 at 13:41
In the DB, change the data type, from TIME to VARCHAR, and it is not saved either, so it does not recognize the first two columns – Kvothe_0077 Sep 18 '17 at 14:27

import excel to mysql Error

2 Answers2