Con la orden LOAD DATA LOCAL INFILE podemos insertar registros, de forma masiva, en una tabla de MySQL procedentes de un archivo de texto.
Cuando se especifica LOCAL, el fichero es leído por el programa cliente en el equipo que se ejecuta el cliente y posteriormente se envían los datos al servidor. Si no se especifica LOCAL, el fichero deberá estar ubicado en el servidor y es leído directamente por el servidor.
Cuando los ficheros están ubicados en el servidor, deberemos tener presente lo siguiente:
- Si se utiliza una ruta absoluta al fichero, el servidor usará la ruta especificada.
- Si se utiliza una ruta relativa, el servidor busca el fichero relativo al directorio de datos del servidor.
- Si se proporciona un nombre de fichero sin especificar ruta, el servidor buscará el archivo en el directorio de la base de datos actual, en la cual se pretenden insertar los registros.
Actualmente, MySQL por motivos de seguridad, deshabilita por defecto, la posibilidad de utilizar la orden LOAD DATA LOCAL INFILE.
LOCAL funcionará sólo si servidor y cliente se han configurado para poder utilizarlo.
Para solucionarlo voy a proponer dos opciones que he probado.
- Desde el cliente de MySQL:
Con esta opción, no modificamos la configuración por defecto del servidor MySQL de forma permanente.
Al hacerlo desde la consola de MySQL estamos modificando la variable local_infile durante el tiempo que dure la sesión. Podremos insertar los registros que deseemos de forma masiva pero cuando cerremos el cliente, prevalecerá la configuración por defecto del servidor MySQL.La orden a ejecutar para habilitar local_infile como usuario, por ejemplo usuario1, es la siguiente:
$ mysql -u usuario1 -p --local-infile
En los ejemplos siguientes vamos anexar registros desde un archivo de texto llamado registros_tabla_articulos.txt a una tabla llamada articulos.
Para el ejemplo en cuestión hemos creado una tabla llamada articulos con la siguiente orden:
CREATE TABLE articulos ( COD_ART CHAR(2), NOMBRE_ART VARCHAR(20), CIUDAD VARCHAR(10) );
Y he aquí un archivo de texto de ejemplo con los registros a anexar en la tabla:
A1 CLASIFICADORA MADRID A2 PERFORADORA MALAGA A3 LECTORA CACERES A4 CONSOLA CACERES A5 MEZCLADORA SEVILLA A6 TERMINAL BARCELONA A7 CINTA SEVILLA
El archivo ha sido creado en un editor de texto con las siguientes características:
- Cada campo está separado por una tabulación (todos los espacios en blanco son tabulaciones).
- Tras el último carácter del último campo de cada registro insertar salto de línea.
- Tras el último carácter del último campo de la última línea no puede haber salto de línea.
Si el archivo de texto de ejemplo ha sido generado en un sistema Windows, deberemos añadir LINES TERMINATED BY ‘\r\n’ ya que Windows usa dos caracteres como terminador de línea. Algunos programas, como Wordpad, pueden usar ‘\r’ como terminador de línea. La orden para añadir los registros de este ejemplo sería la siguiente:mysql>LOAD DATA LOCAL INFILE '/home/usuario/registros_tabla_articulos.txt' INTO TABLE articulos LINES TERMINATED BY '\r\n';
Si el archivo de texto de ejemplo ha sido generado en un sistema Linux utilizaremos LINES TERMINATED BY ‘\n’. La orden para añadir los registros de este ejemplo sería la siguiente:
mysql>LOAD DATA LOCAL INFILE '/home/usuario/registros_tabla_articulos.txt' INTO TABLE articulos LINES TERMINATED BY '\n';
Veamos otro ejemplo creando una tabla llamada direcciones con la siguiente orden:
CREATE TABLE direcciones( NOMBRE CHAR(50), APELLIDO1 VARCHAR(50), DOMICILIO VARCHAR (150), CIUDAD VARCHAR(50) );
Y he aquí un archivo de texto de ejemplo con los registros a anexar en la tabla direcciones:
"José Carlos","Gil","C/sin nombre, 25","Málaga" "Alberto Jesús","Rodríguez","C/La nueva, 25, 1º - A","Ciudad Real" "Pedro","González","Avda. La larga, 3","Avila" "Juan José","Del Castillo","Bda. La Salud, bloque 4 - Puerta 3","Santa Cruz"
Para este tipo de archivo el delimitador de campo será la coma (,) y utilizaremos el parámetro FIELDS TERMINATED BY ‘,’ para indicarlo, y para que no se interpreten las comas que puedan contener los campos como delimitador de campo, encerraremos los campos entre dobles comillas (") utilizando el parámetro ENCLOSED BY ‘"’.
La orden para añadir los registros desde archivos generados en sistemas Windows la orden es la siguiente:mysql>LOAD DATA LOCAL INFILE '/home/usuario/registros_tabla_direcciones.txt' INTO TABLE direcciones FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Si el archivo se ha generado en un sistema Linux, la orden es la siguiente:
mysql>LOAD DATA LOCAL INFILE '/home/usuario/registros_tabla_direcciones.txt' INTO TABLE direcciones FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
- Desde el servidor de MySQL:
Con esta opción cada vez que arranca el servicio de MySQL, establecemos en ON la variable local-infile, es decir habilitaremos por defecto LOAD DATA LOCAL INFILE.Para lograrlo, editaremos el archivo /etc/mysql/my.cnf, buscaremos la etiqueta [mysql] y a continuación añadimos la línea local-infile = 1, si es que no existe. Si existe nos aseguraremos de cambiarla de 0 a 1.
El archivo debería de quedar tal que así:... [mysql] local-infile = 1 ...
El cambio realizado en /etc/mysql/my.cnf tiene efecto inmediato, no hay que reiniciar el servicio de MySQL, pero sí tendremos que reiniciar el cliente para que surta efecto el cambio.
Espero que este artículo os haya sido de utilidad. Si pensáis que podéis colaborar para mejorar este artículo, que hay algo erróneo en él o simplemente deseáis comentarlo, por favor, dejad vuestra opinión más abajo.
Publicaciones Relacionadas
Problemas con USB3 en Linux: habilitar IOMMU (6)
MariaDB: Cómo instalar el plugin ed25519 (0)
Cambio de dominio en WordPress (0)- Eliminar o controlar las revisiones de las entradas (0)
- Activar la reparación y optimización de la base de datos (0)
Configurar proxy para wget (0)
El editor de texto vi (0)
Cómo instalar el navegador web Opera en Debian (16)
Franklin
Sr J. Carlos, espero esté muy bien. Muchas gracias por este post que es de gran ayuda.
Actualmente tengo un ejecución que se venía comportando bien desde hace años en un dominio de mi cliente. Hace unos días este cliente me pidió cambiar la aplicación desarrollada a otro dominio y al ejecutarla me muestra el siguiente error:
LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile
El hosting que usamos es CPanel bajo Linux, pero no sé cómo habilitar dicha función de Mysql.
No se si estoy equivocado en qué debo hacer pero si usted puede ayudarme se lo sabría agradecer.
Saludos!
J. Carlos
Hola Franklin,
Gracias a Dios, estoy muy bien, espero que usted también esté bien y en estos días de pandemia, también su familia y amigos.
Como puede ver en el artículo, la solución desde la consola o editando el archivo /etc/mysql/my.cnf es simple, pero debido a que usted trabaja a través de cPanel, de momento no sabría decirle como hacerlo. Depende en gran grado de la libertad que su proveedor de servicios le de a través de cPanel. No obstante, intentaré buscarle una solución a través de cPanel.
Un saludo y espere la contestación a este comentario.
Pablo
Hola J. Carlos excelente post me ayudo bastante y seguí paso a paso cada uno de las instrucciones (muy útiles)
Solo que tengo un inconveniente y ya de ahí no he avanzado no se que es lo que estoy haciendo mal, ya que quiero importar un archivo de texto en linux a una tabla en mysql pero no me deja y no se porque no se si me podria orientar o saber usted que es lo que esta mal dentro de mi sintaxis ya que me lanza lo siguiente:
LOAD DATA LOCAL_INFILE ‘/web/Apache2/htdocs/SO/reporpccl/PRUEBA_FTP.TXT’ INTO TABLE Prueba1Reportes LINES TERMINATED BY ‘\n’;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘LOCAL_INFILE ‘/web/Apache2/htdocs/SO/reporpccl/PRUEBA_FTP.TXT’ INTO TABLE Prueba’ at line 1
Muchas gracias y saludos.
J. Carlos
Hola Pablo,
Gracias por tu crítica y por visitar Zeppelinux.
Con respecto a tu error, veo que en la sintaxis que has empleado, utilizas LOCAL_INFILE y en realidad es LOCAL INFILE (sin el guion bajo).
Espero que tu problema solo sea ese.
Un saludo
newentu
Buenos días Maestro, tengo un drama con esto, recién estoy comenzando con SQL, pero esto de LOAD INFILE me tiene loco, no me resulta. Uso Linux con la distribuicion LinuxMint y la version de Mysql8
+————————-+
| VERSION() |
+————————-+
| 8.0.21-0ubuntu0.20.04.3 |
+————————-+
1 row in set (0.00 sec)
al ejecutar el comando para cargar un archivo en una tabla me sale esto:
mysql> LOAD DATA LOCAL INFILE «userpiolas.txt» INTO TABLE usuarios;
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
mysql>
He seguido su consejo, pero en los directorios que me muestra:
newentu@newentu-mint:/etc/mysql$ ls
conf.d debian.cnf debian-start my.cnf my.cnf.fallback mysql.cnf mysql.conf.d
en algunos me dice que es solo lectura Read Only y no me deja editarlos.
ademas revise esos archivos y en ninguna parte aparece el item local-infile.
Lo curioso es que uso otro equipo con UBUNTU 18 y MySqL 5 y ahi si me funciona sin problema, carga el archivo OK,
pero igual no sé donde se debe editar el fichero de local-infile.
Ejecute mysqld –verbose –help y me sale un extenso instrutctivo a modo lectura y ahi me sale la opcion local-infile en TRUE (equipo con Ubuntu 18)
pero COMO PUEDO EDITARLO, DONDE DEBO EDITARLO,QUE DIRECTORIO, en su ejemplo lo seguí y no aparece y no se cual de todos se podria modificar.
si me pudiera ayudar con esto, estare muy agradecido.
atentamente
NEWEN
J. Carlos
Hola newentu,
Me gustaría saber si has probado los dos métodos, si te ha funcionado el primero y si en el método 2º, editando my.cnf lo has hecho como root. Por ejemplo:
Solo hay que editar /etc/mysql/my.cnf
Un saludo y espero tu respuesta.
Martín
Hola J. Carlos:
Gran artículo técnico.
Antes escribió estas líneas:
No obstante, intentaré buscarle una solución a través de cPanel.
¿Ha encontrado una solución para hacerlo con cpanel y phpmyadmin?
LLevo subiendo ficheros muchos años y es la primera vez que me sale este error::
#1148 – El comando usado no es permitido con esta versión de MariaDB
El problema es que solo conozco la forma de subir los ficheros con cpanel.
J. Carlos
Hola Martín,
Perdona el retraso.
Estuve haciendo pruebas con mi proveedor de servicios web y no logro encontrar nada relativo a la configuración de LOAD DATA LOCAL INFILE ni por medio de cPanel, ni por phpMyAdmin ni tampoco navegando por la estructura de directorios de mi sitio. En este último punto, busque archivos de configuración de MySQL y no encontré nada.
Creo que si es tu caso, deberás ponerte en contacto con tus proveedores y que el administrador te lo habilite.
Recuerdo que en mi sitio, tuve que ponerme en contactos con ellos para que me instalasen un certificado para habilitar HTTPS y me lo resolvieron rápido.
Inténtalo y me cuentas.
Un saludo
J. Carlos
Hola de nuevo Martín,
Se me ha encendido una bombillita en la cabeza y recordé que en phpMyadmin, hay una pestaña con todas las variables de MySQL. He buscado la variable local infile y existe. Es más en Filtros busca variables que contengan la palabra: local y te será más cómodo buscarla.
Puedes habilitarla (ON) o deshabilitarla (OFF).
Espero que te sirva.
Saludos de nuevo.
Guido
Excelente. Me fue muy util.
J. Carlos
Gracias Guido por tu comentario.