Importar|Exportar ficheros en MySQL Breve información que contiene la sintaxis con su explicación para la importación y exportación de ficheros en MySQL, incluye ejemplos probados en MySQL 5.6.12 usando WAMP Server 2.4 Al final de documento se incluye un pequeño glosario y un tip para realizar una correcta importación de ficheros.

Quethzel quethzel@gmail.com 16 de Septiembre del 2013

Lunes, 16 de septiembre de 2013

IMPORTAR DATOS A UNA TABLA MySQL DESDE UN FICHERO EXTERNO

MySQL permite importar y exportar datos desde o hacia ficheros de texto en diferentes formatos como .txt o .csv. Esto

puede ser útil para exportar los datos de una Base de Datos hacia un fichero que pueda ser procesado por otra aplicación,

para realizar copias de seguridad de la información de nuestras tablas o para importar datos hacia nuestras tablas.

La instrucción LOAD DATA INFILE permite cargar datos desde un fichero externo y volcar su contenido dentro de una

tabla.

Sintaxis: LOAD DATA [LOCAL] INFILE 'file_name.txt'

[REPLACE | IGNORE]

INTO TABLE tbl_name

[FIELDS

[TERMINATED BY '\t']

[[OPTIONALLY] ENCLOSED BY '']

[ESCAPED BY '\\' ]

]

[LINES

[STARTING BY '']

[TERMINATED BY '\n']

]

[IGNORE number LINES]

[(col_name,...)]

Explicación de la sintaxis: LOCAL – Indicar que el fichero a importar esta en nuestro ordenador y no en el servidor donde se encuentra la base de

datos.

INFILE ‘ruta/del/fichero.ext’ – Indica cual es la ruta de fichero a importar, se debe indicar el formato del fichero.

[REPLACE | IGNORE] – REPLACE Indica que agregara la fila remplazando la existente. Por ejemplo si un dato dentro del

fichero tiene el mismo ID que un dato dentro de la tabla este será remplazado por el valor del fichero. IGNORE Indica que

serán ignoradas las filas del fichero que violen alguna restricción (constraint). Por ejemplo que el tipo de dato en el fichero

no coincide con el tipo de dato establecido en la base de datos.

Solo se puede utilizar REPLACE o IGNORE, pero no ambos. Estas cláusulas son útiles cuando se importan datos desde un

fichero hacia una tabla que ya contiene datos, por ejemplo si tenemos una tabla productos y el fichero a importar contiene

algunos productos con datos actualizados sería conveniente utilizar REPLACE de esta forma se actualizaría los campos que

ya esté en la tabla y se agregarían los nuevos productos, pero si no estamos seguros de que datos son los más actualizados

podríamos utilizar IGNORE de esta forma solo se agregaran los datos que no existan en la tabla o que no entren en

conflictos con otros datos de la tabla.

INTO TABLE nom_tabla – Indica el nombre de la tabla sobre la cual se volcaran los datos del fichero a importar.

Adicionalmente se puede indicar el conjunto de caracteres CHARACTER SET utf8 y el cotejamiento COLLATION

utf8_general_ci. Se puede utilizar otros conjuntos de caracteres como Unicode o ANSI. Recomiendo el conjunto de

caracteres utf8 y el cotejamiento utf8_general_ci para que puedas visualizar correctamente acentos y otros caracteres

especiales. Si no se establece el conjunto de caracteres es común que se ignoren todos los datos del fichero que contengan

acentos o caracteres especiales. Por ejemplo si el fichero contiene la palabra México solo se agregar M en la tabla ya que

desconocerá el carácter é.

Lunes, 16 de septiembre de 2013

FIELDS – Se refiere a las opciones de cada columna.

TERMINATED BY ‘carácter’ – Indica cual es el carácter de separación de columna. Por defecto el carácter utilizado

es tabulador, pero podemos establecer otro carácter como: ( , ) ( ; ) ( : ) ( | ).

[OPTIONALLY] ENCLOSED BY ‘carácter’ – Indica que los valores de cada columna serán entrecomillados. Por

defecto no se entrecomilla ninguna columna, pero se puede utilizar cualquier carácter. La palabra OPTIONALLY

indica que solo se entrecomillaran los valores de tipo texto y fecha.

ESCAPED BY ‘carácter’ – Indica el carácter que se utilizara para escapar aquellos caracteres que puedan dificultar

la lectura posterior del fichero. Como por ejemplo si utilizamos el carácter ( , ) como carácter de separación de

columna y nos encontramos con una columna de descripción que tiene un carácter ( , ) como parte del dato a

insertar, este ocasionara un problema ya que será interpretado como carácter de separación de columna. Para

evitar este error utilizamos la cláusula ESCAPED BY ‘carácter’ así podemos escapar esa ( , ) que forma parte de

nuestros datos anteponiendo el carácter de escape ( \ ).

LINES – Se refiere a las opciones para cada fila.

STARTING BY – Indica el carácter de inicio de cada línea. Por defecto no se utiliza ningún carácter.

TERMINATED BY ‘carácter’ – Permite elegir el carácter para indicar la terminación de cada línea. El carácter por

defecto es el retorno de línea (enter). También se puede utilizar ( \r\n ).

IGNORE n LINES – Indica el número de líneas a ignorar al iniciar la lectura del fichero. Por ejemplo si se desea agregar una

descripción sobre que contiene el fichero, que codificación utiliza, a que tabla debe de ir, etc. Esta descripción se agrega

en las primeras n líneas del fichero y al importar l fichero se hace uso de IGNORE n LINES. De esta manera serán ignoradas

las primeras n líneas del fichero.

[ (COL_NAME)… ] – Indica el orden en que se deberán almacenar los datos del fichero con respecto a la tabla. Por defecto

los datos de fichero serán almacenados en la tabla secuencialmente, es decir MySQL insertara el primer dato de la primera

columna y el primer renglón en la primera columna y primero renglón de la tabla, pero si el fichero tiene una secuencia

diferente debemos usar (col_name…) para indicar el orden en que se deben almacenar los datos del fichero dentro de la

tabla por ejempló:

Capos dentro de la tabla:

ID nombre segundoNom apellidoPat apellidoMat

Campos dentro del fichero: Viveros, Ramírez, , Viviana, 074A323

En este caso debemos especificar en la sentencia SQL el orden de inserción de los datos del fichero de la siguiente forma:

(apellidoPat, apellidoMat, segundoNom, nombre, ID);

Ya que el primer dato dentro del fichero corresponde al apellido paterno dentro de la tabla, el segundo al apellido

materno, el tercer campo al segundo nombre, el cuarto campo al nombre y el ultimo al ID.

Ejemplo básico de importación de un fichero .txt:

LOAD DATA LOCAL INFILE “c:/wamp/www/proyecto/usuarios.txt” INTO TABLE usuarios FIELDS

TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;

Lunes, 16 de septiembre de 2013

Ejemplo más específico (altamente recomendable):

La sentencia SQL:

LOAD DATA LOCAL INFILE "C:/wamp/www/estados.txt" INTO TABLE estados CHARACTER SET UTF8

FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 2 LINES (stt_nom);

NOTA: Ignora las 2 primeras líneas que son la descripción y una línea en blanco y especifica la columna stt_nom ya que la

primera columna en la BD es el ID de estado (stt_id). Agrega la correcta visualización de caracteres usando UTF8.

Lunes, 16 de septiembre de 2013

EXPORTAR DATOS DESDE UNA TABLA MySQL HACIA UN FICHERO EXTERNO

MySQL también permite generar ficheros de texto plano a partir de una tabla.

Sintaxis: SELECT [campos...] [INTO OUTFIELD ‘file_name’ export_options]

Explicación de la sintaxis: SELECT [campos…] – Selecciona los campos que se desean importar. También se puede utilizar * para indicar la selección de todos los campos de la tabla. INTO OUTFIELD ‘file_name’ – Indica el nombre del fichero a crear (no debe existir). Las opciones de exportación son las mismas que se describieron en el apartado de importación de datos a MySQL desde un fichero externo.

Tip: Se recomienda que al importar los datos desde un fichero .txt primero se verifique que la codificación del fichero este en

UFT8 ya que aun cuando se especifique en la consulta de MySQL UTF8, si el fichero de texto no está codificado en UTF8

la importación de los datos posiblemente contenga errores en cada palabra que tenga acentos o algún carácter especial.

Es común que se utilice un bloc de notas para la creación del fichero, por lo cual al guardar el fichero por default el bloc

de notas mantiene una codificación ANSI, es por ello que se debe cambiar la codificación a UTF8. Si usted se encuentra

en un sistema Linux o Unix puede utilizar Geany para crear sus ficheros.

Desde mi punto de vista ya seas usuario Windows, Linux, Unix recomiendo Geany ya que considero que es mejor que

bloc de notas y más cómodo de utilizar.

Página oficial de genay: http://www.geany.org/Main/HomePage Ejemplo no probado: El siguiente ejemplo no ha sido probado, solo se inclue como información adicional.

Uso de variables: Suponiendo que uno de nuestros datos dentro del fichero es de tipo fecha con formato DD/MM/YY y la

tabla maneja un DATE YYY-MM-DD. Podemos utilizar variables de usuario anteponiendo el símbolo @ al campo que

deseamos cambiar y almacenando su valor en una variable temporal. Ejemplo:

Datos del fichero: Viviana, Viveros, Ramírez, 23/07/12,

Sentecia SQL:

LOAD DATA LOCAL INFILE "C:/wamp/www/clientes.txt" INTO TABLE clientes CHARACTER SET UTF8

FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'(nom, apPaterno, apMaterno, @fecha);

SET fecha_nacimiento = str_to_date(@fecha, ‘%d-%m-%y’);

Lunes, 16 de septiembre de 2013

Glosario: csv – del inglés comma-separated values. Es un tipo de documento en formato abierto sencillo para representar datos en forma de tabla, en las que las columnas se separan por comas o punto y coma y las filas por saltos de línea. Los campos que contengan una coma, un salto de línea o una comilla doble deben ser encerrados entre comillas dobles. Más información en: http://es.wikipedia.org/wiki/CSV

Constraint – Se refiere a las restricciones de SQL que se utilizan para especificar las reglas para los datos en una tabla. Si hay cualquier violación entre la restricción y la acción de datos, la acción se interrumpe por la restricción. Las violaciones se pueden dar por tratar de introducir un dato de tipo X en un capo de otro tipo Y. Por ejemplo insertar un VARCHAR en un INT o violar alguna de las siguientes restricciones: NOT NULL - Indica que una columna no puede almacenar el valor NULL. UNIQUE - Asegura que cada fila de una columna tenga un valor único. PRIMARY KEY - Una combinación de NOT NULL y UNIQUE. Asegura que una columna (o combinación de dos o más columnas) tiene una identidad única que ayuda a encontrar un registro en particular en una tabla más fácil y rápidamente. FOREIGN KEY - Asegurar la integridad referencial de los datos en una tabla para que coincida con los valores de otra tabla. CHECK - Asegura que el valor de una columna cumple una condición específica. DEFAULT - Establece un valor por defecto cuando no se especifica ningún valor para esa columna. Existen otras restricciones que no se describen en este documento como ENUM, SET, ZEROFILL, etc. Más información en: http://dev.mysql.com/doc/refman/5.0/es/constraint-invalid-data.html