Mysql Blog en Español

Yo quería importar los siguientes datos de una hoja de cálculo en mi tabla de base de datos

Code:

Después de guardar la hoja de cálculo en formato CSV (delimitado por comas) fue mi primer intento de utilizar la sintaxis del comando "LOAD DATA".

Code:

Había leído que esto rara vez funciona, así no me sorprendió encontrar problemas en los datos insertados (aunque por lo menos hizo algunos intentos de poblar la tabla desde el archivo).
Por lo tanto, decidí utilizar el mecanismo de IMPORT en phpMyAdmin, después de la primera selección de la tabla necesario. Una opción útil es especificar aquí el número de registros para saltar desde el comienzo del archivo - esto me permitió pasar el registro cabecera (nombres de campo) de que no desea importar. Sin embargo las opciones por defecto me falle. El "FIELDS TERMINATED BY" caja tiene un defecto de ";" cuando lo necesitamos ",". De todos modos, después de corregir esto mediante la introducción de la coma, estaba en condiciones de importar los registros. Sin embargo todavía había un problema - la fecha de todas las columnas mostró 00/00/00. Esto se debe a que MySQL usa el formato de una fecha diferente a Microsoft Excel. Cómo encontrar la solución a esto? Volví a la hoja Excel y cambiar el formato del campo a la fecha yyyy-mm-dd (formato de ingles, es aaaa-mm-dd en espanol?) antes de guardar como un archivo CSV (delimitado por comas) y pasar por el proceso de nuevo. Al final he podido importar los datos que yo quería.

Otras soluciones podrían haber incluyó el uso de OpenOffice para crear la hoja de cálculo y csv, usando ODBC preguntas en Mysql, usando Navicat, php o utilizando un programa para leer e importar los datos.
Espero a ver estas opciones en futuros artículos.

Por cierto, en el pasado, cuando hacia la transferencia de datos de hojas de cálculo en bases de datos he utilizado la siguiente técnica. Uso la función de Excel "concatenar" para crear una declaración de sql INSERT en una sola línea y, a continuación, copia la función abajo. Por ejemplo, para insertar los dos primeros campos sólo, yo haría lo siguiente en Excel y, a continuación, copia los consultas de sql en Mysql.

Code:

Esto resulta en lo siguiente

Code:

Recuerda, sin embargo, tener cuidado en relación con diferentes tipos de datos; por ejemplo, tendrás que incluir el apóstrofe en la concatenación cuando el campo es del formato varchar.

Un requisito muy común cuando se trabaja con datos, es encontrar entradas duplicadas. El lenguaje SQL es muy potente en hacerlo, pero mucha gente prefiere utilizar Excel para encontrar duplicados. Mi opinión es que esta es buena para los pequeños conjuntos de datos (menos de 10.000 filas). Puedes utilizar el formato condicional (por ejemplo, con este: =CONTAR.SI(a:A,a2) > 1)), o
puedes utilizar el Filtro/avanzado menú de opciones para filtrar y copiar a otro lugar (hay que activar la casilla para copiar sólo registros únicos). Más modernas versiones de Excel tienen ahora un comando en el menú de Datos: "quitar duplicados"

En MySQL (y, de hecho, otras versiones de SQL) la forma más común de encontrar duplicados de las entradas de campo es el uso de la cláusula GROUP BY. Por ejemplo, para devolver las entradas para el campo "job" de la tabla emp que tienen más de 1 fila/empleado registrado contra ese "job" en particular:

Code:

Si querías devolver registros registró más de 3 veces en la tabla solo necesitas modificar la cláusula HAVING.

Code:

Por cierto, si sólo querías devolver distinta campo(s) en una tabla, puedes utilizar GROUP BY sin realizar suma. Ahora esto es todo muy bien, pero suponiendo que tienes la obligación de devolver todos los campos en un registro se produce cuando el identificador/campo es un duplicado. Podemos hacer esto por un subconsulta de IN:

Code:

Todo muy bien hasta ahora, pero vamos a introducir una tabla segunda. Supongamos que queremos seleccionar los registros que se producen dos veces en las dos tablas. Podemos hacerlo utilizando el operador UNION para devolver todos los registros de ambas tablas. (recuerde usar UNION ALL, si no los duplicados sería reprimida). Entonces pongamos esto en un "inline view" y, a continuación, utilizar la cláusula GROUP BY para seleccionar sólo duplicados

Code:

No estoy seguro de cómo se enfoque en Excel! Aunque es bastante fácil en Mysql!

Diviértete con Mysql!

=====================================
Olivier [Visitor]
Hola!
Excelente artículo la verdad. Y felicidades por el blog.

Estoy interesado en el primer ejemplo. Sabrías cómo eliminar los duplicados encontrados? Es decir, eliminar todos los duplicados encontrados excepto uno. Si se han encontrado 3 filas con un valor igual y 5 con otro valor igual pues eliminar un total de 6 respectivas filas y dejar las 2 originales.

¿Seria muy complicado en mysql? Imagino que a mucha gente lo que le interesa con los duplicados es eliminarlos :-)

Un saludo.

--
Hola Olivier! Gracias por tu pregunta. Me costo un poco en pensar sobre esto.
Es possible eliminar los duplicados bastante facil, con condicion de que haya Primary Key.
Suponer que tenemos estas filas (en la table dup_take)
+-----+-------+----+
| sno | cno | id |
+-----+-------+----+
| 1 | CS112 | 1 |
| 1 | CS113 | 2 |
| 1 | CS114 | 3 |
| 2 | CS114 | 12 |
| 2 | CS112 | 4 |
| 3 | CS112 | 5 |
| 3 | CS114 | 6 |
| 4 | CS113 | 8 |
| 4 | CS112 | 7 |
| 5 | CS113 | 9 |
| 6 | CS113 | 10 |
| 6 | CS114 | 11 |
+-----+-------+----+
Queremos quedar con una fila de cada sno. Bueno, primero hagamos un "temporary table":

create temporary table to_delete (sno tinyint, cno varchar(5), min_id smallint);

Luego podemos insertar estos:

insert into to_delete (sno, min_id)
select sno, MIN(id) from dup_take group by sno having count(*) > 1;

Entonces podemos usar la palabra clave EXISTS (funciona como una prueba de existencia). Es decir, solo eliminar los registros donde "EXISTS" mas de una fila y donde el id no es el original:

delete from dup_take
where exists (
select * from to_delete
where to_delete.sno = dup_take.sno
and to_delete.min_id <> dup_take.id);

Por fin tenemos:
+-----+-------+----+
| sno | cno | id |
+-----+-------+----+
| 1 | CS112 | 1 |
| 2 | CS112 | 4 |
| 3 | CS112 | 5 |
| 4 | CS112 | 7 |
| 5 | CS113 | 9 |
| 6 | CS113 | 10 |
+-----+-------+----+

Saludos
Mark

Una tarea común en la limpieza de datos es comprobar que un campo de texto contiene datos válidos. Caracteres de control, iniciales o números pueden ser incómodas valores en algunos campos.
Si no son más que comprobar que la celda contiene texto, en contraposición a los números, me parece más fácil de hacer esto en Excel y exportar los datos si es necesario ..
Para los interesados, la fórmula para hacer esto sería algo como:
=IF(ISTEXT(A3),"Good","False") -- en ingles
=SI(ESTEXTO(A3),"exito","falso") -- en español
Sin embargo, tenemos varias opciones de abordar este problema en Mysql
Una interesante forma de hacer esto podría ser para comprobar que las letras contienen una vocal.
Sin embargo, si nos limitamos a usar el función "substring" de la siguiente manera, podríamos estar en torno a un largo tiempo. El siguiente código muestra la forma en que se iniciaría este:

Code:

Una forma mejor sería utilizar la función LOCATE para ver si una palabra contiene una vocal. Si la cadena no se encuentra una vocal entonces LOCATE devuelve cero.

Code:

Aunque he usado LOCATE yo podría fácilmente haber utilizado la función INSTR en su lugar.
Recuerda, sin embargo, que la sintaxis es diferente para esto:
instr(str, substr)
[ INSTR retorna la posición de la primera ocurrencia de la subcadena substr en la cadena str Es lo mismo que la forma de dos argumentos de LOCATE(), excepto que el orden de los argumentos es inverso. ]

Sin embargo, de otra manera sería usar NOT LIKE.

Code:

Algunos de vosotros os habeis dado cuenta de hay fallas en el uso de las anteriores. ¿Qué hay de caso? Tal vez algunos sólo tienen las entradas iniciales en mayusculas? En el campo de texto no puede haber siglas o incluso la palabra inglesa "rhythm" (no contiene vocales - en castellano no hay palabras sin vocales, con una excepción, la preposición "Y").
Tal vez una mejor forma sería la de asegurarse de que el valor de la letra tiene un buen valor ASCII. Por ejemplo:

Code:

Si los números son válidos aquí, podemos probar para valores entre 48 y 122. Aquí hay enlacea a la tabla de valores ascii.

tablas de ascii

ascii tables faq

Por supuesto, el código anterior sólo funciona en pruebas el primer carácter de la cadena. Con el fin de probar todas las características de la cadena tenemos que iterar a través de una cadena de caracteres. Voy a mostrar cómo hacer esto en mi próxima entrada del blog.

Por último, para los programadores avanzados, hay una gran capacidad en MySQL - la capacidad de utilizar las expresiones regulares. Una vez más, esto necesita una mayor explicación de lo que yo tengo tiempo para de esta entrada. Sin embargo, hay una sección dedicada a esto en el manual de Mysql páginas. Aqui es el enlace:
mysql manual regexp

Se puede aprender mas sobre expressiones regulares aqui:

mas sobre expressiones regulares

Si decides utilizar expresiones regulares, recuerda que a pesar de que puede ser muy poderoso, también pueden golpear problemas de rendimiento en tablas grandes.

Bueno, en conclusión, hay muchas maneras de enfocar este tipo de tarea en MySQL. Espero que toma nota de lo anterior han dado una buena idea de cómo podemos hacer esto.