Announcement

Collapse
No announcement yet.

Importing Excel data to table in MySQL

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Importing Excel data to table in MySQL

    Good afternoon group;
    I would like to be collaborated with a bug that throws me an application I work on.

    I explain what I want to do: I want to pass data from an Excel sheet to a MySql table, for this I'm using a control type application to load the file and a blank application, to process the data I insert them in the database.

    When running the application generates the following error 'Error An error occurred while accessing the database Incorrect integer value: ' ' for column ' IDSEDE ' at row 1 '.



    This is the code that I use in the blank application:

    PHP Code:
    sc_include_lib ("excel"); //Incluimos la libreria excel para poder procesar nuestro archivo 
    $path=[vruta]; // Capturamos la variable global que llamamos ruta, se envia desde el control 
    $msj=""// una variable para mensaje 
    $objPHPExcel=PHPExcel_IOFactory::load($path); //Cargamos nuestro archivo a la libreria 
    foreach ($objPHPExcel->getWorksheetIterator() as $worksheet// Aqui recorre las hojas del archivo, en nuestro caso esta en la hoja 1 toda la información 
    //
        
    $worksheet $objPHPExcel->getActiveSheet(); 
        
    $worksheetTitle $worksheet->getTitle(); //Titulo de la hoja excel 
        
    $highestRow $worksheet->getHighestRow(); 
        
    $highestColumn $worksheet->getHighestColumn(); 
        
    $highestColumnIndex PHPExcel_Cell::columnIndexFromString($highestColumn); 
        
    $nrColumns ord($highestColumn) - 64//Recorremos las filas que tienen datos, empieza en la numero 1 porque la 1 tiene los titulos de las columnas 
        //echo $worksheetTitle.' '.$highestRow.' '.$highestColumn.' '.$highestColumnIndex.'<br />'; 
        //echo $nrColumns.'<br />'; 

        //sc_alert($worksheetTitle.' '.$highestRow.' '.$highestColumn.' '.$highestColumnIndex.'<br />'.' '. $nrColumns.'<br />');

        
    for ($row=2;$row<= $highestRow; ++ $row
        { 
            
    $cell $worksheet -> getCellByColumnAndRow (0$row); 
            
    $Aticket $cell ->getValue(); // Numero de ticket 
            
    $cell $worksheet -> getCellByColumnAndRow (1$row); 
            
    $Bsede $cell ->getValue(); //ID de la sede 
            
    $cell $worksheet -> getCellByColumnAndRow (2$row); 
            
    $Cempresa $cell ->getValue(); //ID de la empresa
            
    $cell $worksheet -> getCellByColumnAndRow (3$row); 
            
    $Dnombres $cell ->getValue(); //Nombres y Apellidos
            
    $cell $worksheet -> getCellByColumnAndRow (4$row); 
            
    $Ecedula $cell ->getValue(); //Número de Cedula del trabajador 
            
    $cell $worksheet -> getCellByColumnAndRow (5$row); 
            
    $Ffechai $cell ->getValue(); //Fecha y hora de ingreso del trabajador
            
    $cell $worksheet -> getCellByColumnAndRow (6$row); 
            
    $Gfechaf $cell ->getValue(); //Fecha y hora de salida del trabajador
            
    $cell $worksheet -> getCellByColumnAndRow (7$row); 
            
    $Hmotivo $cell ->getValue(); //Motivo de ingreso del trabajador
            
    $cell $worksheet -> getCellByColumnAndRow (8$row); 
            
    $Iautorizado $cell ->getValue(); //Quien autoriza ingreso del trabajador
            
    $cell $worksheet -> getCellByColumnAndRow (9$row); 
            
    $Jestado $cell ->getValue(); //Fecha y hora de ingreso del trabajador

            
    $fi = new datetime($Ffechai);
            
    $var date_format($fi'Y-m-d H:i');
            
    //$fi = date("Y-m-d H:i");

            
    $ff = new datetime($Gfechaf);
            
    $var1 date_format($ff'Y-m-d H:i');
            
    //$ff = date("Y-m-d H:i");


            
    $insert_table 'CONTROLINGRESOTERCEROS'// Table name 
            
    $insert_fields = array( // Field list, add as many as needed 
                
    'TICKET' => "'$Aticket'"
                
    'IDSEDE' => "'$Bsede'"
                
    'IDEMPRESA' => "'$Cempresa'"
                
    'NOMBRES' => "'$Dnombres'"
                
    'CEDULA' => "'$Ecedula'",
                
    'FECHAI' => "'$var'",
                
    'FECHAF' => "'$var1'",
                
    'MOTIVO' => "'$Hmotivo'",
                
    'AUTORIZADO' => "'$Iautorizado'",
                
    'ESTADO' => "'$Jestado'",
            );
            
    // Insert record 

            
    $insert_sql 'INSERT INTO ' 
                
    $insert_table ' (' implode(', 'array_keys($insert_fields)) . ')' 
                
    ' VALUES (' implode(', 'array_values($insert_fields)) . ')'

            
    sc_exec_sql($insert_sql); 
        } 

    if(
    $msj!=""

        
    $msj "Error al procesar los datos!";
        
    sc_alert($msj); 
        
    sc_redir(CIngresoTerceros);
    } else 

        
    $msj "Archivo procesado correctamente!";
        
    sc_alert($msj); 
        
    sc_redir(CIngresoTerceros);
    }

    //Archivo control_importaExcel 

    //$vruta= $this->Ini->path_doc."/".(archivo);//en esta variable SC guarda la ruta de los archivos que se suben 

    //{ruta}=$vruta;// lo enviamos al campo tipo text para que nos muestre la ruta donde se encuentra en archivo. 




    I would appreciate a lot of suggestions on how to solve this error. Thanks a lot.
    Error Control application Run control application

  • #2
    Funny thing processing with PHPExcel: while column count starts at 0, row count starts at 1. I suspect it's trying to insert your title row values into the db, giving you that error.

    Either you start with row 2 or delete the title row and continue to treat row 1 as your first data row.

    Comment


    • #3
      Sorry, just noticed you are in fact starting with row 2. Can you troubleshoot by echoing every cell read to the screen or inserting into a debug table in your database. That will help zero in on exactly where the problem is. On the face of it, I don't see why what you're experiencing is happening.

      Comment


      • #4
        Originally posted by scriptcaser View Post
        Funny thing processing with PHPExcel: while column count starts at 0, row count starts at 1. I suspect it's trying to insert your title row values into the db, giving you that error.

        Either you start with row 2 or delete the title row and continue to treat row 1 as your first data row.
        thank you very much, I will verify

        Comment


        • #5
          Originally posted by scriptcaser View Post
          Sorry, just noticed you are in fact starting with row 2. Can you troubleshoot by echoing every cell read to the screen or inserting into a debug table in your database. That will help zero in on exactly where the problem is. On the face of it, I don't see why what you're experiencing is happening.
          Hello, thank you very much for your answers, I tell you that even if I throw that error, it saves me the data in the database, what I have not been able to know is why it occurs.
          Please I need an orientation.

          Comment


          • #6
            What I mean is to log the actual values it’s reading so you can trace.

            Try starting with row 3 and after that 4 and see what values it shows. Perhaps you have a hidden row? You can also try deleting the header row and start with row 1.

            Comment


            • #7
              I have not used import from excel for a long time, but I remember fixing a similar problem using addslashes in front of the column that was causing me this problem
              quote +

              for ($ row = 2; $ row <= $ totalRows; $ row ++) {
              for ($ colum = 0; $ colum <$ totalColumns; $ colum ++) {


              if ($ == $ totalColumns colum-1) {

              $ rowDate. = "'" .addslashes ($ objPHPExcel-> getActiveSheet () -> getCellByColumnAndRow ($ colum, $ row) -> getValue ()). "'";
              } Else {
              $ rowDate. = "'" .addslashes ($ objPHPExcel-> getActiveSheet () -> getCellByColumnAndRow ($ colum, $ row) -> getValue ()). "',";

              }
              }

              Comment


              • #8
                Yes... I've not done it for a while but I definitely agree you need addslashes as described by nsch2308.
                Also check for spaces in the field in case it is being parsed incorrectly

                My experience was that the best way to debug this is to set the Application in debug mode. Then you can see the SQL it is trying to send to the server. Back-tracking from there can give you a fairly quick view of where the problem is.

                Comment


                • #9
                  it is good to use addslashes, trim, and replace in the job with the import excel successively

                  Comment


                  • #10
                    johnmebao please tell me is this working anymore. I want to do similar thing in my project.
                    Regards,
                    Lokesh Patel
                    Igetis Solutions
                    Pune, MH, INDIA

                    Comment

                    Working...
                    X