14 Lenguaje SQL Importar y exportar datos

MySQL permite copiar tablas en diferentes formatos de texto, así como importar datos a partir de fichero de texto en diferentes formatos.

Esto se puede usar para exportar los datos de nuestras bases de datos a otras aplicaciones, o bien para importar datos desde otras fuentes a nuestras tablas. También se puede usar para hacer copias de seguridad y restaurarlas posteriormente.

Exportar a otros ficheros

Para extraer datos desde una base de datos a un fichero se usa la sentencia SELECT ... INTO OUTFILE.

El resto de las cláusulas de SELECT siguen siendo aplicables, la única diferencia es que la salida de la selección se envía a un fichero en lugar de hacerlo a la consola.

La sintaxis de la parte INTO OUTFILE es:

[INTO OUTFILE 'file_name' export_options]

file_name es el nombre del fichero de salida. Ese fichero no debe existir, ya que en caso contrario la sentencia fallará.

En cuanto a las opciones de exportación son las mismas que para las cláusulas FIELDS y LINES de LOAD DATA. Su sintaxis es:

    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES 
        [STARTING BY '']    
        [TERMINATED BY '\n']
    ]

Estas cláusulas nos permiten crear diferentes formatos de ficheros de salida.

La cláusula FIELDS se refiere a las opciones de cada columna:

  • TERMINATED BY 'carácter': nos permite elegir el carácter delimitador que se usará para separar cada columna. Por defecto, el valor que se usa es el tabulador, pero podemos usar ';', ',', etc.
  • [OPTIONALLY] ENCLOSED BY 'carácter': sirve para elegir el carácter usado para entrecomillar cada columna. Por defecto no se entrecomilla ninguna columna, pero podemos elegir cualquier carácter. Si se añade la palabra OPTIONALLY sólo se entrecomillarán las columnas de texto y fecha.
  • ESCAPED BY 'carácter': sirve para indicar el carácter que se usará para escapar aquellos caracteres que pueden dificultar la lectura posterior del fichero. Por ejemplo, si terminamos las columnas con ',' y no las entrecomillamos, un carácter ',' dentro de una columna de texto se interpretará como un separador de columnas. Para evitar esto se puede escapar esa coma con otro carácter. Por defecto se usa el carácter '\'.

La cláusula LINES se refiere a las opciones para cada fila:

  • STARTING BY 'carácter': permite seleccionar el carácter para comenzar cada línea. Por defecto no se usa ningún carácter para ello.
  • TERMINATED BY 'carácter': permite elegir el carácter para terminar cada línea. Por defecto es el retorno de línea, pero se puede usar cualquier otro carácter o caracteres, por ejemplo '\r\n'.

Por ejemplo, para obtener un fichero de texto a partir de la tabla 'gente', con las columnas delimitadas por ';', entrecomillando las columnas de texto con '"' y separando cada fila por la secuencia '\r\n', usaremos la siguiente sentecia:

mysql> SELECT * FROM gente
    -> INTO OUTFILE "gente.txt" 
    -> FIELDS TERMINATED BY ';' 
    -> OPTIONALLY ENCLOSED BY '\"' 
    -> LINES TERMINATED BY '\n\r';
Query OK, 5 rows affected (0.00 sec)

mysql>

El fichero de salida tendrá este aspecto:

"Fulano";"1974-04-12"
"Mengano";"1978-06-15"
"Tulano";"2000-12-02"
"Pegano";"1993-02-10"
"Mengano";\N

La fecha para "Mengano" era NULL, para indicarlo se muestra el valor \N.

Importar a partir de ficheros externos

Por supuesto, el proceso contrario también es posible. Podemos leer el contenido de un fichero de texto en una tabla. El fichero origen puede haber sido creado mediante una sentecia SELECT ... INTO OUTFILE, o mediante cualquier otro medio.

Para hacerlo disponemos de la sentencia LOAD DATA, cuya sintaxis más simple es:

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,...)]

La cláusula LOCAL indica, si aparece, que el fichero está en el ordenador del cliente. Si no se especifica el fichero de texto se buscará en el servidor, concretamente en el mismo directorio donde esté la base de datos. Esto nos permite importar datos desde nuestro ordenador en un sistema en que el servidor de MySQL se encuentra en otra máquina.

Las cláusulas REPLACE e IGNORE afectan al modo en que se tratan las filas leídas que contengan el mismo valor para una clave principal o única para una fila existente en la tabla. Si se especifica REPLACE se sustituirá la fila actual por la leída. Si se especifica IGNORE el valor leído será ignorado.

La parte INTO TABLA tbl_name indica en qué tabla se insertarán los valores leídos.

No comentaremos mucho sobre las cláusulas FIELDS y LINES ya que su significado es el mismo que vimos para la sentencia SELECT ... INTO OUTFILE. Estas sentencias nos permiten interpretar correctamente cada fila y cada columna, adaptándonos al formato del fichero de texto de entrada.

La misma utilidad tiene la cláusula IGNORE número LINES, que nos permite que las primeras número líneas no se interpreten como datos a importar. Es frecuente que los ficheros de texto que usaremos como fuente de datos contengan algunas cabeceras que expliquen el contenido del fichero, o que contengan los nombres de cada columna. Usando esta cláusula podemos ignorarlas.

La última parte nos permite indicar la columna a la que será asignada cada una de las columnas leídas, esto será útil si el orden de las columnas en la tabla no es el mismo que en el fichero de texto, o si el número de columnas es diferente en ambos.

Por ejemplo, supongamos que queremos añadir el contenido de este fichero a la tabla "gente":

Fichero de datos de "gente"
fecha,nombre
2004-03-15,Xulana
2000-09-09,Con Clase
1998-04-15,Pingrana

Como vemos, hay dos filas al principio que no contienen datos válidos, las columnas están separadas con comas y, como hemos editado el fichero con el "notepad", las líneas terminan con "\n\r". La sentencia adecuada para leer los datos es:

mysql> LOAD DATA INFILE "gente.txt"
    -> INTO TABLE gente
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\r\n'
    -> IGNORE 2 LINES
    -> (fecha,nombre);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql>

El nuevo contenido de la tabla es:

mysql> SELECT * FROM gente;
+-----------+------------+
| nombre    | fecha      |
+-----------+------------+
| Fulano    | 1974-04-12 |
| Mengano   | 1978-06-15 |
| Tulano    | 2000-12-02 |
| Pegano    | 1993-02-10 |
| Mengano   | NULL       |
| Xulana    | 2004-03-15 |
| Con Clase | 2000-09-09 |
| Pingrana  | 1998-04-15 |
+-----------+------------+
8 rows in set (0.00 sec)

mysql>

Comentarios de los usuarios (7)

Luis Alberto Vaca Diaz
2011-11-04 18:19:06

Hola, bueno lejos de ser un comentario es una pregunta. Me gustaria saber si existen las referencias cruzadas en MySQL, si es asi, me gustaria si podeis enviarme la sintaxis y un ejmplo de como hacerlo.

De antemano muchisimas gracias.

Esperare respuesta.

Saludos

Steven R. Davidson
2011-11-05 17:40:52

Hola Luis Alberto,

Hablamos de este tema en el capítulo 12, cuyo enlace es: http://mysql.conclase.net/curso/index.php?cap=012a#inicio

Espero que esto te oriente.

Steven

bpalma
2012-02-16 15:58:24

Hola, estoy migrando mi base de datos a otro servidor, con este paso no he tenido problemas, ahora bien quiero saber si puedo transferir los usuarios del servidor mysql que esta en mi viejo servidor? si es posible podrían darme una luz?

de antemano gracias!!!

Salvador Pozo
2012-02-20 19:08:02

Hola:

No sé si tendrás acceso a las tablas que contienen los datos de usuarios y privilegios.

Estas tablas están en la base de datos mysql, y son:

user, columns_priv, procs_priv y tables_priv.

Seguramente tengas que copiar otras tablas, como proc, db o host.

Si no tienes acceso, lo veo más complicado.

Hasta pronto.

Ray Islas
2012-06-09 21:05:15

Salvador, excelente tutorial !! Me ha servido mucho la info que has compartido. Cuando empeze a dar mis primeros pasos en MySql estudie cada una de tus paginas, y aun hoy en dia, vuelvo aqui cuando me sale alguna duda.

Muchas Gracias

Daniel Posada
2012-10-18 00:26:58

buenas Tardes.

De antemano los felicito por esta pagina, esta demasiado completa y clara

Tengo una pregunta, tengo unos datos con una clave primaria, pero necesito que salgan partidos, es decir

Esto es lo que tengo

+---------+---------+------+

|cedula1 | ref1 | ref2 |

+---------+---------+------+

Lo que necesito es

+---------+---------+

|cedula1 | ref1 |

|cedula2 | ref2 |

+---------+---------+

Me podueden colaborar con esto, muchas gracias

adrian merino
2014-03-21 21:17:45

haber quien puede ayudarme, lo que pasa es que estoy bajando informacion electoral de los portales del ife para meterlos a una base de datos en mysql, pero el metodo que utilizo es manual por medio del programa MySQL workbench, tomo los registros de un excel los cuales ya fueron acomodados en la hoja de calculo en cada una de las tabals que insertare y selecciono en el excel los registro y le doy copiar, luego me voy a la tabla donde quiero insertarlos y le pongo paste y se pegan los registros y ya doy comit y se guardan. este metodo lo use para guardar la informacion de todo sonora sin ningun problema me trae las consultas en el sistema, luego continue con el estado de baja california, pero en las consultas grandes se queda trabado baja california y sonora me lo trar de maravilla. alguien que pueda ayudarme con esto darme una idea de solucion por que no le encuentro. volvi a quitar lso indices de las tablas y lso volvi a crear y todo saigue igual.