Importar y exportar datos en Postgres y MySQL

Muchas veces necesitamos exportar o importar desde un archivo texto hacia una tabla de nuestra base de datos, vamos a mostrar como hacerlo en Postgres y en MySQL.

Exportar de Postgres a un archivo texto


Lo mas sencillo que se puede hacer es exportar todos los datos de una tabla a un archivo texto separando los campos por TABS (TSV), la sentencia sería algo así:

copy tabla1 to '/tmp/tabla1.txt';

Si se quiere exportar especificando un separador, como por ejemplo la "coma" para obtener un CSV:

copy tabla1 to '/tmp/tabla1.csv' with delimiter ',';

Podemos especificar además que campos de la tabla queremos exportar:

copy tabla1(campo1, campo2) to '/tmp/tabla1.csv' with delimiter ',';

Vamos a añadir los nombres de los campos en la primera linea del archivo y queremos encerrar los campos de texto entre comillas (útil cuando los campos de texto puedan tener saltos de línea o caracteres que coincidan con el separador):

copy tabla1(campo1, campo2) to '/tmp/tabla1.csv' with delimiter ',' csv header quote '"'; 

Existen otras opciones, por ejemplo, desde Postgres 8.3 se puede exportar el resultado se una consulta:

copy (select * from t1 inner join t2 on t1.c1 = t2.c1 where t1.c2=0) to '/tmp/tabla1.csv' with delimiter ',' csv header;

Más en https://www.postgresql.org/docs/11/sql-copy.html

Importar a Postgres desde un archivo texto


Vamos a ir poniendo los ejemplos para importar los mismos archivos que hemos ido exportando.

Para importar un archivo separado por TABS sin encabezado, en esta caso el archivo tiene que tener la misma cantidad de campos que la tabla donde vamos a poner los datos y deben estar en el mismo orden:

copy tabla1 from '/tmp/tabla1.txt';

Si queremos importar un archivo cuyo separador de campos es la coma:

copy tabla1 from '/tmp/tabla1.csv' with delimiter ',';

Podemos especificar que campos queremos importar, esto es muy útil cuando el archivo de texto no tiene todos los campos de la tabla o no estan en el mismo orden en que estan en la tabla:

copy tabla1(campo1, campo2) from '/tmp/tabla1.csv' with delimiter ',';

Por último si el archivo tiene en la primera línea los nombres de los campos y los campos texto puedan estan encerrados entre comillas:

copy tabla1(campo1, campo2) from '/tmp/tabla1.csv' with delimiter ',' csv header quote '"';

Sobre el separador 


El separador debe ser un caracter que no cause muchos conflictos, si va a exportar o importar datos numéricos el separador puede ser el TAB, pero en otros casos es necesario usar un separdor propio, una buena elección es la barra vertical (PIPE) "|", no se puede hacer uso de caracteres extraños ya que Postgres los interpreta como más de un caracter.

Exportar de MySQL a un archivo texto


En MySQL vamos a ir directamente al grano, la idea es inicialmente plantearnos que queremos exportar y hacer una consulta para obtener esos datos, por ejemplo:

select * from tabla1 where tabla1.c1=0 

Después especificamos a que archivo queremos exportarlo:

into outfile '/tmp/archivo1.csv'

Y por último especificamos las opciones de que separador queremos usar, si vamos a encerrar los archivos texto entre comillas y cual será el separador de filas, normalmente se debe usar para este último parametro un salto de línea (\n en caso de sistemas UNIX o \r\n para sistemas Windows):

fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';

Uniéndolo todo:

select * from tabla1 where tabla1.c1=0 into outfile '/tmp/archivo1.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';

Ver más en: https://dev.mysql.com/doc/refman/8.0/en/select-into.html

Importar a MySQL desde un archivo texto


Esta si es una sentencia más especifica para este objetivo, LOAD DATA:

load data infile '/tmp/archivo1.csv' into table tabla1 fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
 
Si el archivo no está en el servidor y estamos accediendo a la base de datos desde un mysql-client remoto (en nuestra máquina de escritorio o nuestra laptop, por ejemplo) entonces debemos especificar que el mismo se encuentra local.

load data local infile '/tmp/archivo1.csv' into table tabla1 fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';

Si el servidor de mysql esta corriendo con la directiva  --secure-file-priv tendríamos que cargar los datos con LOCAL aunque se encuentre en el servidor o colocar el archivo en el directorio que tenga especificado el servidor para estos propósitos, lo cual podemos saber corriendo la sentencia 


SHOW VARIABLES LIKE "secure_file_priv";


La ubicación varía en dependencia de la versión y la configuración pero en muchos casos es

/var/lib/mysql-files/

En caso de ser un archivo de gran tamaño lo mejor es colocarlo en el directorio configurado para este propósito en la variable  secure_file_priv ya que la opción LOCAL es un poco más lenta.

Si el archivo tiene una línea  de encabezado podemos ignorarla de la siguiente manera

load data infile '/var/lib/mysql-files/archivo1.csv' into table tabla1 fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' IGNORE 1 LINES;

Ver más en: https://dev.mysql.com/doc/refman/8.0/en/load-data.html

Espero que sea de ayuda.

Comentarios

Jonathan Isla S. ha dicho que…
Muchas gracias, de momento es lo más completo y claro que he encontrado!

Saludos!
Jonathan Isla S. ha dicho que…
Este comentario ha sido eliminado por un administrador del blog.
Unknown ha dicho que…
podras darme un ejemplo de como insertar mis datos que se encuentran separados con un pipe a mi tabla de postgresql

90909|andrea|hola|9087, poe ejemplo como inserto esos datos aun tabla???
Unknown ha dicho que…
podras darme un ejemplo de como insertar mis datos que se encuentran separados con un pipe a mi tabla de postgresql

90909|andrea|hola|9087, poe ejemplo como inserto esos datos aun tabla???
wazcuy ha dicho que…
copy mitabla(id, nombre, mensaje, cantidad) from '/tmp/archivo.txt' with delimiter as '|';

Entradas populares de este blog

Verificando la carga del servidor