Insercion CSV a SQL server 2005 con PHP

gunsofledz · #1 (**permalink**) 02/04/2014, 20:01

Hola que tal, gracias por pasar, me gusatria saber que es lo que esta pasando con mis inserciones, ya que me arroja este error de conversion..

Warning: mssql_query() [function.mssql-query]: message: Error de conversi�n al convertir una cadena de caracteres a datetime. (severity 16) in C:\newwamp\www\le\subir_archivo.php on line 32

Warning: mssql_query() [function.mssql-query]: Query failed in C:\newwamp\www\le\subir_archivo.php on line 32

Muchisimas veces... como 100(ya que inserto mas de 300 registros)...

este es mi codigo...

Código PHP:

 
<!DOCTYPE html> 
<html> 
    <head> 
        <title>Subir archivo</title> 
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" /> 
        <script src="js/ajax.js"></script> 
    </head> 
    <body> 
         
<div align="center"> 
    <div>IMPORTACION DE REGISTROS DE ASISTENCIA</div> 
  <br> 
<form action="subir_archivo.php" method="post" enctype="multipart/form-data"> 
    <label for="id">Direcci&oacute;n: </label> 
   <select name="nombreDireccion" id="cont" onchange="load(this.value)"> 
       <option value="0">- Seleccione Direcci&oacute;n -</option> 
       <?php 
      require_once 'conexion/conexion.php'; 
      conectar(); 
       $query = mssql_query("SELECT cod_dir, nom_direccion FROM direccion"); 
     
    if(mssql_num_rows($query) != 0) 
             while ($fila=mssql_fetch_array($query)) { 
                        echo '<option value="'.$fila['cod_dir'].'">'.$fila['nom_direccion'].'</option>'; 
                                                    } 
        else{ 
 
        echo '<option value="0">No hay registros para mostrar.</option>'; 
            } 
    ?> 
    
    </select>  
   <div id="nombreDepto"> 
 
 
 
   </div> 
 
        <br> 
     <label for="id">No. Periodo </label> 
     <input type="text" name="periodo" id="periodo" size="8"  maxlength="10"/> 
      
  <label for="archivo">Archivo:</label> 
  <input type="file" name="archivo" id="archivo" /> 
  <br/> 
  <input type="submit" value="Enviar" /> 
</form> 
</div> 
             
    </body> 
</html>

Código PHP:

 
 <?php 
  require_once 'conexion/conexion.php'; 
      conectar(); 
 
 
  
   $direc=$_POST['nombreDireccion']; 
   $depo=$_POST['nombreDepto']; 
   $periodo=$_POST['periodo']; 
   $file = $_FILES['archivo']['tmp_name']; 
 
 
 
   
  $ext_permitidas = array('csv'); 
  $partes_nombre = explode('.', $nombre); 
  $extension = end( $partes_nombre ); 
  $ext_correcta = in_array($extension, $ext_permitidas); 
  
 
  $handle =fopen($file, "r"); 
    while(($fileop = fgetcsv($handle,1000,",")) !==false) 
    { 
 
        $uno = $fileop[0];$dos = $fileop[2]; $tres = $fileop[3]; 
        $cuatro = $fileop[4];$cinco = $fileop[5]; $seis = $fileop[6]; 
        $siete = $fileop[7]; $ocho = $fileop[8];$nueve = $fileop[9]; 
        $diez = $fileop[10]; 
 
        $sql= mssql_query("INSERT INTO asistencia VALUES($uno, '$dos','$tres','$cuatro','$cinco', 
          '$seis','$siete','$ocho','$nueve','$diez','$direc','$depo',$periodo)"); 
         
   } 
  if(!$sql) 
      { 
        echo "Inserci&oacute;n realizada con &eacute;xito!!"; 
        echo '<a href="cargar_exe.php">Regresar</a>'; 
 
        } 
  else{ 
 
         echo "error... :("; 
        echo '<a href="cargar_exe.php">Regresar</a>'; 
      }      
 
 
  
 
?>

Y mi base de datos es esta...

id_cap_asis int identity
no_empleado int
nombre nvarchar
fecha datetime
hora_entrada datetime
hora_salida datetime
reg_entrada datetime
reg_salida datetime
ent_tar datetime
sal_ant datetime
falta -9 nvarchar
cod_dir nvarchar
cod_dep nvarchar
periodo int

Gracias por su ayuda...!!!

maczy · #2 (**permalink**) 02/04/2014, 20:18

Es por el formato en el que estas utilizando para insertar la fecha, proba cambiando el formato de insercion a ver que pasa.

gunsofledz · #3 (**permalink**) 03/04/2014, 10:19

Gracias por pasar maczy!, lo del formato de la fecha lo tenia pensado pero en la inserción SQL lo ajusta automáticamente así que no cambio el formato al sacarlos de la columna e insertarlos. Hay elementos que si los inserta, solo faltan uno o dos pero realmente inserta los elementos en su respectivo lugar.-..

Esto es lo que inserto...

121052,Norma Araceli Bautista S,10/03/2014,08:00,15:30,07:55,15:53,,,
121052,Norma Araceli Bautista S,11/03/2014,08:00,15:30,07:58,15:42,,,
135290,Karen Suhey Rios Suasti,06/03/2014,07:00,14:00,,,,,Falta
135290,Karen Suhey Rios Suasti,07/03/2014,07:00,14:00,,,,,Falta
135290,Karen Suhey Rios Suasti,10/03/2014,07:00,14:00,,,,,Falta
135290,Karen Suhey Rios Suasti,11/03/2014,07:00,14:00,,,,,Falta
135290,Karen Suhey Rios Suasti,12/03/2014,07:00,14:00,,,,,Falta
135290,Karen Suhey Rios Suasti,13/03/2014,07:00,14:00,,,,,Falta
135290,Karen Suhey Rios Suasti,14/03/2014,07:00,14:00,,,,,Falta
139163,Martha J. Hernandez Ruiz,03/03/2014,08:00,15:30,08:48,15:56,00:48,,
139163,Martha J. Hernandez Ruiz,04/03/2014,08:00,15:30,,18:39,01:00,,
139163,Martha J. Hernandez Ruiz,05/03/2014,08:00,15:30,,16:27,01:00,,
139163,Martha J. Hernandez Ruiz,06/03/2014,08:00,15:30,,17:34,01:00,,
139163,Martha J. Hernandez Ruiz,07/03/2014,08:00,15:30,,16:27,01:00,,
139163,Martha J. Hernandez Ruiz,10/03/2014,08:00,15:30,,18:37,01:00,,
139163,Martha J. Hernandez Ruiz,11/03/2014,08:00,15:30,,19:14,01:00,,
139163,Martha J. Hernandez Ruiz,12/03/2014,08:00,15:30,08:22,16:29,00:22,,
139163,Martha J. Hernandez Ruiz,13/03/2014,08:00,15:30,09:02,19:25,01:02,,
139163,Martha J. Hernandez Ruiz,14/03/2014,08:00,15:30,08:48,19:14,00:48,,
154266,Ma. Guadalupe Barron Mal,03/03/2014,08:00,15:30,07:51,15:39,,,
154266,Ma. Guadalupe Barron Mal,04/03/2014,09:00,15:00,08:50,15:05,,,
154266,Ma. Guadalupe Barron Mal,04/03/2014,18:00,20:00,17:53,20:16,,,
154266,Ma. Guadalupe Barron Mal,05/03/2014,08:00,15:30,07:49,15:33,,,
154266,Ma. Guadalupe Barron Mal,06/03/2014,08:00,15:30,,,,,Falta
154266,Ma. Guadalupe Barron Mal,07/03/2014,08:00,15:30,07:52,15:36,,,
154266,Ma. Guadalupe Barron Mal,10/03/2014,08:00,15:30,07:55,15:50,,,
154266,Ma. Guadalupe Barron Mal,11/03/2014,09:00,15:00,08:49,15:02,,,
154266,Ma. Guadalupe Barron Mal,11/03/2014,18:00,20:00,17:53,21:21,,,
154266,Ma. Guadalupe Barron Mal,12/03/2014,08:00,15:30,07:54,19:02,,,
154266,Ma. Guadalupe Barron Mal,13/03/2014,08:00,15:30,09:36,,01:36,01:00,
154266,Ma. Guadalupe Barron Mal,14/03/2014,08:00,15:30,08:48,15:03,00:48,00:27,
165048,Josue Becerra Bautista,03/03/2014,08:30,16:00,,,,,Falta
165048,Josue Becerra Bautista,04/03/2014,08:30,16:00,,,,,Falta
165048,Josue Becerra Bautista,05/03/2014,08:30,16:00,,,,,Falta
165048,Josue Becerra Bautista,06/03/2014,08:30,16:00,,,,,Falta
165048,Josue Becerra Bautista,13/03/2014,08:30,16:00,,,,,Falta
165048,Josue Becerra Bautista,14/03/2014,08:30,16:00,,,,,Falta
167774,Danae Aguilar Espinosa,03/03/2014,08:00,15:30,08:00,15:55,,,
167774,Danae Aguilar Espinosa,04/03/2014,08:00,15:30,08:32,20:05,00:32,,
167774,Danae Aguilar Espinosa,05/03/2014,08:00,15:30,07:31,20:23,,,
167774,Danae Aguilar Espinosa,06/03/2014,08:00,15:30,07:30,17:17,,,
167774,Danae Aguilar Espinosa,07/03/2014,08:00,15:30,07:39,17:51,,,
167774,Danae Aguilar Espinosa,10/03/2014,08:00,15:30,07:33,18:02,,,
167774,Danae Aguilar Espinosa,11/03/2014,08:00,15:30,07:35,20:07,,,
167774,Danae Aguilar Espinosa,12/03/2014,08:00,15:30,07:44,18:14,,,
167774,Danae Aguilar Espinosa,13/03/2014,08:00,15:30,07:26,20:07,,,
167774,Danae Aguilar Espinosa,14/03/2014,08:00,15:30,07:44,16:08,,,
172616,Aldo Alejandro Tinajero,03/03/2014,08:00,15:30,07:59,16:00,,,
172616,Aldo Alejandro Tinajero,04/03/2014,08:00,15:30,07:49,15:31,,,
172616,Aldo Alejandro Tinajero,05/03/2014,08:00,15:30,07:55,15:32,,,
172616,Aldo Alejandro Tinajero,06/03/2014,08:00,15:30,07:54,15:31,,,
172616,Aldo Alejandro Tinajero,07/03/2014,08:00,15:30,08:00,15:31,,,
172616,Aldo Alejandro Tinajero,10/03/2014,08:00,15:30,07:46,16:03,,,
40402,Crsitina Coellar Quiroz,03/03/2014,09:00,15:00,08:50,15:12,,,
40402,Crsitina Coellar Quiroz,03/03/2014,18:00,20:00,17:39,22:10,,,
40402,Crsitina Coellar Quiroz,04/03/2014,09:00,15:00,08:41,15:28,,,
40402,Crsitina Coellar Quiroz,04/03/2014,18:00,20:00,17:43,20:23,,,
40402,Crsitina Coellar Quiroz,10/03/2014,09:00,15:00,08:59,15:11,,,
40402,Crsitina Coellar Quiroz,10/03/2014,18:00,20:00,17:48,20:21,,,
40402,Crsitina Coellar Quiroz,11/03/2014,09:00,15:00,08:52,15:01,,,
40402,Crsitina Coellar Quiroz,11/03/2014,18:00,20:00,17:40,20:03,,,
40402,Crsitina Coellar Quiroz,12/03/2014,09:00,15:00,08:44,15:08,,,
40402,Crsitina Coellar Quiroz,12/03/2014,18:00,20:00,17:23,20:07,,,
40402,Crsitina Coellar Quiroz,13/03/2014,09:00,15:00,08:38,15:15,,,
40402,Crsitina Coellar Quiroz,13/03/2014,18:00,20:00,17:53,20:03,,,
40402,Crsitina Coellar Quiroz,14/03/2014,09:00,15:00,08:46,15:14,,,
40402,Crsitina Coellar Quiroz,14/03/2014,18:00,20:00,,,,,Falta
80850,Mónica Alejandra Aguilló,03/03/2014,08:00,15:30,,,,,Falta
80850,Mónica Alejandra Aguilló,04/03/2014,08:00,15:30,,,,,Falta
80850,Mónica Alejandra Aguilló,05/03/2014,08:00,15:30,,,,,Falta
80850,Mónica Alejandra Aguilló,13/03/2014,08:00,15:30,,,,,Falta
80850,Mónica Alejandra Aguilló,14/03/2014,08:00,15:30,,,,,Falta
94162,Oswaldo Lindero Torres,03/03/2014,08:00,15:30,,,,,Falta
94162,Oswaldo Lindero Torres,04/03/2014,08:00,15:30,,,,,Falta
94162,Oswaldo Lindero Torres,05/03/2014,08:00,15:30,,,,,Falta
94162,Oswaldo Lindero Torres,06/03/2014,08:00,15:30,,,,,Falta
94162,Oswaldo Lindero Torres,07/03/2014,08:00,15:30,,,,,Falta
94162,Oswaldo Lindero Torres,10/03/2014,08:00,15:30,,,,,Falta
94162,Oswaldo Lindero Torres,11/03/2014,08:00,15:30,,,,,Falta
94162,Oswaldo Lindero Torres,12/03/2014,08:00,15:30,,,,,Falta
94162,Oswaldo Lindero Torres,13/03/2014,08:00,15:30,,,,,Falta
94162,Oswaldo Lindero Torres,14/03/2014,08:00,15:30,,,,,Falta
169271,Pedro Tellez Trejo,03/03/2014,08:00,15:30,07:52,15:51,,,
169271,Pedro Tellez Trejo,04/03/2014,08:00,15:30,07:32,15:35,,,
169271,Pedro Tellez Trejo,05/03/2014,08:00,15:30,08:00,16.01,,,,
169271,Pedro Tellez Trejo,13/03/2014,08:00,15:30,07:53,15:55,,,
169271,Pedro Tellez Trejo,14/03/2014,08:00,15:30,08:00,16:19,,,
314,Juan Carlos Lira Cabrera,03/03/2014,08:00,15:30,,,,,Falta
314,Juan Carlos Lira Cabrera,04/03/2014,08:00,15:30,,,,,Falta
314,Juan Carlos Lira Cabrera,05/03/2014,08:00,15:30,,,,,Falta
314,Juan Carlos Lira Cabrera,06/03/2014,08:00,15:30,,,,,Falta
314,Juan Carlos Lira Cabrera,07/03/2014,08:00,15:30,,,,,Falta
314,Juan Carlos Lira Cabrera,10/03/2014,08:00,15:30,,,,,Falta
314,Juan Carlos Lira Cabrera,11/03/2014,08:00,15:30,,,,,Falta
314,Juan Carlos Lira Cabrera,12/03/2014,08:00,15:30,,,,,Falta
314,Juan Carlos Lira Cabrera,13/03/2014,08:00,15:30,,,,,Falta
314,Juan Carlos Lira Cabrera,14/03/2014,08:00,15:30,,,,,Falta
9345,Olivia Mata Torres,03/03/2014,08:00,15:30,07:52,15:34,,,
9345,Olivia Mata Torres,04/03/2014,08:00,15:30,07:55,16:05,,,
9345,Olivia Mata Torres,05/03/2014,09:00,15:00,,,,,Falta
9345,Olivia Mata Torres,05/03/2014,18:00,20:00,,,,,Falta
9345,Olivia Mata Torres,06/03/2014,08:00,15:30,07:46,15:31,,,
9345,Olivia Mata Torres,07/03/2014,08:00,15:30,07:56,15:37,,,
9345,Olivia Mata Torres,13/03/2014,08:00,15:30,07:53,15:32,,,
9345,Olivia Mata Torres,14/03/2014,08:00,15:30,07:55,15:03,,00:27,
41882,Elsa Morales Reza,03/03/2014,08:00,14:00,07:50,14:03,,,
41882,Elsa Morales Reza,03/03/2014,18:00,20:00,17:31,21:05,,,
41882,Elsa Morales Reza,04/03/2014,08:00,14:00,07:50,14:17,,,
41882,Elsa Morales Reza,04/03/2014,18:00,20:00,17:30,20:27,,,
41882,Elsa Morales Reza,10/03/2014,08:00,14:00,07:49,14:17,,,
41882,Elsa Morales Reza,10/03/2014,18:00,20:00,17:43,20:23,,,
41882,Elsa Morales Reza,11/03/2014,08:00,14:00,07:52,14:03,,,
41882,Elsa Morales Reza,11/03/2014,18:00,20:00,17:56,20:46,,,
41882,Elsa Morales Reza,12/03/2014,08:00,14:00,07:56,14:00,,,
41882,Elsa Morales Reza,12/03/2014,18:00,20:00,17:53,20:04,,,
41882,Elsa Morales Reza,13/03/2014,08:00,14:00,07:55,14:39,,,
41882,Elsa Morales Reza,13/03/2014,18:00,20:00,17:43,20:11,,,
41882,Elsa Morales Reza,14/03/2014,08:00,15:30,07:51,15:45,,,
55941,Griselda Berenice Olguín,03/03/2014,08:00,15:30,,,,,Falta
55941,Griselda Berenice Olguín,04/03/2014,08:00,15:30,,,,,Falta
55941,Griselda Berenice Olguín,12/03/2014,08:00,15:30,,,,,Falta
55941,Griselda Berenice Olguín,13/03/2014,09:00,15:00,,,,,Falta
55941,Griselda Berenice Olguín,13/03/2014,18:00,20:00,,,,,Falta
55941,Griselda Berenice Olguín,14/03/2014,08:00,15:30,,,,,Falta

Esta es la estructura de mi BD SQL server 2005, hice unos cambios en los primeros dos campos, los puse en numeric...

id_cap_asis numeric() identity
no_empleado numeric
nombre nvarchar
fecha datetime
hora_entrada datetime
hora_salida datetime
reg_entrada datetime
reg_salida datetime
ent_tar datetime
sal_ant datetime
falta -9 nvarchar
cod_dir nvarchar
cod_dep nvarchar
periodo int

Gracias por sus respuestas!!!