El día de hoy tuve la necesidad de importar archivo de texto plano (.txt) a una base de datos. El inconveniente es que estos separadas sus columnas por tabulación y la base es tan grande que excel simplemente no puede abrirlo.
Mi intención inicial era convertir estos archivos a .csv e importarlos desde phpMyAdmin o algún cliente de base de datos, pero al ser muy grande pensé «no soy el primero con esta necesidad, por lo que debe existir un método de importación desde la consola», que es el método más rápido y confiable pienso yo.
Por ello me di a la tarea de buscar en la documentación de MySQL y me encontre con la syntax LOAD DATA INFILE que permite el importar bases de datos a partir de un archivo y se ajusta a multiples necesidades.
CONFIGURACIÓN INICIAL
Para que LOAD DATA INFILE función, debes habilitarlo en tu my.cnf de MySQL que generalmente esta en /etc/my.cnf y habilitar este recurso con la siguiente linea de configuración:
local-infile=1
Una mejor configuración quedaría así:
[mysqld] local-infile=1 [mysql] local-infile=1 [client] loose-local-infile=1 local-infile=1
UTILIZANDO LOAD DATA INFILE
Inicialmente nos conectamos a la base de datos:
mysql -u user_mysql -p
Una vez que ingresamos a MySQL seleccionamos la base de datos donde deseamos hacer la importación con el siguiente comando:
use nombre_base_de_datos
Ahora iniciamos la importación:
LOAD DATA LOCAL INFILE '/ruta/fichero/texto_plano.txt' INTO TABLE nombre_tabla FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (campo1, campo2, campo3, etc);
Como notaremos, las columnas podemos personalizar la delimitación en FIEDS TERMINATED BY » para lo que podríamos usar ‘,’ en caso de un archivo separado por comas (.csv) o también ‘;’ en caso de separación por punto y coma, o ‘|’, entre otras. En mi caso particular al estar separado por tabulación use ‘\t’
Adicionalmente podemos definir el fin de la linea con la sentencia LINES TERMINATED BY, en los sistemas mac se usa \r mientas que para windows y linux se usa \n.
Finalmente podemos personalizar los campos de la tabla a los cuales deseamos importar el texto plano, es decir, si tu fichero posee 10 columnas pero tu tabla 15, puedes personalizar estas a cuales columnas deseas se ingresen los datos.
OTRAS PERSONALIZACIONES
En el caso de los archivos .csv estos suelen enmarcar los datos de una columna entre comillas («»), para lo cual debes usar la sentencia ENCLOSED BY ‘»‘ luego de FIELDS TERMINATED BY ‘,’ lo que quedaría así en caso de un .csv delimitado por comas y sus columnas por doble comillas:
LOAD DATA LOCAL INFILE '/ruta/fichero/texto_plano.txt' INTO TABLE nombre_tabla FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (campo1, campo2, campo3, etc);
Otra opción interesante es el poder transformar datos al importar, por ejemplo, la fecha de tu archivo esta en dd/mm/yyyy, pero resulta que la necesitas en formato DATE que es yyyy/mm/dd, esto lo puedes solucionar con la sentencia SET que se usaria asi:
LOAD DATA LOCAL INFILE '/ruta/fichero/texto_plano.txt' INTO TABLE nombre_tabla FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (campo1, campo2, campo3, @fecha, etc) SET fecha= STR_TO_DATE(@fecha, '%Y/%m/%d');
Que hacer para que no ingrese la linea de encabezado? Esa linea que generalmente posee los nombres de las columnas. Esto se soluciona con la sintaxis IGNORE 1 ROWS que se utilizaría así:
LOAD DATA LOCAL INFILE '/ruta/fichero/texto_plano.txt' INTO TABLE nombre_tabla FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (campo1, campo2, campo3, @fecha, etc) SET fecha= STR_TO_DATE(@fecha, '%Y/%m/%d');
En mi caso particular mi base tiene 4 campos de fecha con el formato dd/mm/yyyy mientras que deseo ingresarlo en el formato yyyy/mm/dd que usa el campo DATE de MySQL para mejorar así mis consultas a la base de datos. Mi código final quedo así:
LOAD DATA LOCAL INFILE '/ruta/fichero/texto_plano.txt' INTO TABLE nombre_tabla FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 ROWS (campo1, campo2, campo3, @fecha1, @fecha2, @fecha3, @fecha4, etc) SET fecha1= DATE_FORMAT( STR_TO_DATE(@fecha1, '%d/%m/%Y'), '%Y/%m/%d'), fecha2= DATE_FORMAT( STR_TO_DATE(@fecha2, '%d/%m/%Y'), '%Y/%m/%d'), fecha3= DATE_FORMAT( STR_TO_DATE(@fecha3, '%d/%m/%Y'), '%Y/%m/%d'), fecha4= DATE_FORMAT( STR_TO_DATE(@fecha4, '%d/%m/%Y'), '%Y/%m/%d');
Y así pude importar mi base de datos super fácil y sin morir en el intento 😉
¿Como importo archivos .sql desde la consola?
Para importar archivos .sql desde la consola, muy útil para grandes bases de datos, solo debes usar el siguiente comando:
mysql -u nombreusuario -p basededatos < /ruta/archivo.sql
MySQL solicitara la clave del usuario e iniciara la importación a la basededatos indicada.
¿Como importo archivos .sql de gran tamaño desde la consola?
Si el archivo a importar es muy grande, tal vez prefieras que MySQL omita ciertas validaciones, como indices unicos y llaves foraneas, para que así la importación sea mucho más rápida. Para ello lo mejor es hace la importación desde la consola de MySQL:
mysql -u nombreusuario -p
Luego de iniciar sesión en MySQL, seleccionamos la base de datos en la cual deseamos realizar la importación:
use base_de_datos_a_usar;
Luego realizamos la importación:
SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; source /ruta/archivo.sql
Espero esta información les sea de mucha utilidad como me fue a mi. ¡Feliz semana!