Si tiene datos en un archivo de texto, puede cargarlos fácilmente a una o más tablas de una base de datos.
En la base de datos MySQL (o MariaDB), utilizando el comando «load data infile», puede cargar datos desde un archivo de texto a las tablas.
El comando load data infile proporciona varias opciones flexibles para cargar varios formatos de datos desde un archivo de texto a las tablas.
En este tutorial se tratan los siguientes ejemplos de carga de datos:
- Ejemplo básico para cargar datos desde un archivo de texto
- Cargar datos utilizando la opción «Campos terminados por»
- Cargar datos utilizando la opción «Encerrado por»
- Utilizar el carácter de escape en los datos del archivo de texto
- Cargar datos utilizando la opción «Líneas terminadas por»
- Ignorar el prefijo de línea en el archivo de carga utilizando la opción «Empezando por» Opción
- Ignorar Línea de Encabezado del Archivo de Carga
- Cargar Sólo Columnas Específicas (e Ignorar Otras) del Archivo de Carga
- Utilizar Variable durante la Carga con la Opción «Set»
- Escribir Script de Shell para Cargar Datos del Archivo de Texto
Ejemplo Básico para Cargar Datos del Archivo de Texto
En el siguiente ejemplo, el archivo employee1.txt tiene los valores de los campos separados por tabulador.
# cat employee1.txt 100 Thomas Sales 5000200 Jason Technology 5500300 Mayla Technology 7000400 Nisha Marketing 9500500 Randy Technology 6000
Por defecto, el comando load data infile utiliza TAB como delimitador de campos por defecto.
Primero, ve a la base de datos donde quieres cargar el archivo de texto. En este ejemplo, cargaremos el archivo employee1.txt anterior a la tabla employee ubicada en la base de datos mysql de thegeekstuff.
USE thegeekstuff;
El siguiente comando MySQL cargará los registros del archivo employee1.txt anterior a la tabla employee como se muestra a continuación. Este comando no utiliza ninguna opción extra.
LOAD DATA INFILE 'employee1.txt' INTO TABLE employee;
Nota: En el ejemplo anterior, el comando asume que el archivo employee1.txt ubicado bajo el directorio de la base de datos. Por ejemplo, si está ejecutando el comando anterior en la base de datosgeekstuff, entonces coloque el archivo bajo: /var/lib/mysql/thegeekstuff/
La siguiente será la salida del comando anterior.
Query OK, 5 rows affected (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
En lo anterior:
- La primera línea «Query OK» dice que la consulta se ejecutó sin ningún error. También dice que un total de 5 filas fueron subidas a la tabla. Esto también muestra el tiempo que se ha tardado en cargar los datos del archivo de texto a la tabla en segundos.
- La segunda línea muestra el total de filas cargadas, cuántas se han omitido y cuántos registros han mostrado advertencias durante la carga.
Después de cargar los datos, lo siguiente es lo que veremos en la tabla de empleados.
Nota: Si quieres hacer una copia de seguridad y restaurar toda tu base de datos MySQL, utiliza el comando mysqldump.
Subir datos utilizando la opción «Campos terminados en»
En el siguiente ejemplo, en el archivo de entrada empleado2.txt, los valores de los campos están separados por comas.
Para subir los registros anteriores a la tabla de empleados, utilice el siguiente comando.
Durante la carga, utilizando la opción «CAMPOS TERMINADOS POR», puede especificar el delimitador de campos por comas como se muestra a continuación.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ',';
De nuevo, utilice esta opción sólo cuando los valores de los campos estén separados por cualquier cosa que no sea TAB. Si los campos están terminados por dos puntos, usarás la siguiente opción en el comando anterior:
FIELDS TERMINATED BY ':';
Si eres nuevo en MySQL lee esto: Tutorial de MySQL: Instalar, crear una base de datos y una tabla, insertar y seleccionar registros
Los siguientes son algunos errores básicos que podrían ocurrir durante la carga de MySQL
Error 1: Si el archivo de texto no se encuentra en el directorio adecuado, podría obtener el siguiente mensaje de error «ERROR 13 (HY000) Can’t get stat of (Errcode: 2)».
También puede especificar la ruta completa del archivo en el comando load data infile como se muestra a continuación. Si haces esto, asegúrate de que el archivo puede ser accedido por mysql. Si no es así, cambie la propiedad a mysql apropiadamente. Si no, obtendrá el mensaje de error load data infile permission denied.
MariaDB > LOAD DATA INFILE '/data/employee2.txt' INTO TABLE employee;
Error 2: Si no especifica los campos correctos terminados por, entonces verá algunos problemas en la carga. En este ejemplo, sólo se subió el primer campo «id». El valor de todos los demás campos es NULL. Esto se debe a que el siguiente comando no especifica la opción de campo terminado por, ya que el archivo de entrada tiene una coma como delimitador de campo.
Subir datos usando la opción «Enclosed by»
En el siguiente ejemplo, el archivo de texto de entrada tiene los valores de los campos de texto encerrados entre comillas dobles, es decir, los valores de nombre y departamento tienen comillas dobles alrededor de ellos.
En este caso, utilice la opción «Enclosed by» como se muestra a continuación.
LOAD DATA INFILE 'employee3.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ENCLOSED BY '"';
El comando anterior cargará los registros correctamente como se muestra a continuación por el comando mysql select:
Tenga en cuenta que cuando está combinando campos terminados por y campos encerrados por, no tiene que usar la palabra clave «FIELDS» dos veces como se muestra a continuación, que mostrará el siguiente mensaje de error:
FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"';
Lo anterior mostrará el siguiente error «ERROR 1064 (42000)»:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FIELDS ENCLOSED BY '"'' at line 4
Usando el carácter de escape en los datos del archivo de texto
Digamos que tiene coma en el valor de un campo en particular.
Por ejemplo, en el siguiente ejemplo, el 2º nombre de campo tiene el valor en este formato: «firstname, lastname».
Si cargas el archivo anterior con el siguiente comando, verás que te mostrará «10 advertencias»
Los registros tampoco se cargan correctamente porque hay una coma en el valor de uno de los campos.
Fichero correcto: Para solucionar el problema anterior, utilice la barra invertida (\) delante de la coma en el valor del campo nombre, como se muestra a continuación.
Lo siguiente funcionará esta vez sin ningún error, ya que tenemos \Ncomo carácter de escape.
También puede utilizar un carácter de escape diferente, como se muestra a continuación. En este ejemplo, estamos utilizando ^ como el carácter de escape en lugar del defualt \ /p>
En este caso, utilice la opción «ESCAPED BY» como se muestra a continuación.
LOAD DATA INFILE 'employee41.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ESCAPED BY '\^'
Por favor, tenga en cuenta que algunos de los caracteres no se puede utilizar como carácter de escape. Por ejemplo, si utiliza % como carácter de escape, obtendrá el siguiente mensaje de error.
LOAD DATA INFILE 'employee41.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ESCAPED BY '\%'ERROR 1083 (42000): Field separator argument is not what is expected; check the manual
Subir datos utilizando la opción «Líneas terminadas por»
En lugar de tener todos los registros en una línea separada, también puede tenerlos en la misma línea.
En el siguiente ejemplo, todos y cada uno de los registros están separados por el símbolo |.
Para subir el archivo anterior, utilice la opción líneas terminadas por como se muestra a continuación.
LOAD DATA INFILE 'employee5.txt' INTO TABLE employee FIELDS TERMINATED BY ',' LINES TERMINATED BY '|';
El comando anterior subirá los registros de employee5.txt como se muestra a continuación.
Los siguientes son un par de puntos a tener en cuenta:
- Si el archivo de entrada proviene de una máquina Windows, entonces es posible que desee utilizar esto: LÍNEAS TERMINADAS POR ‘\N-\N-‘
- Si está utilizando un archivo CSV para cargar los datos a la tabla, entonces pruebe uno de estos 1) Líneas terminadas por ‘\r’ 2) Líneas terminadas por ‘\r\n’
Ignorar el prefijo de línea en el archivo de carga usando la opción «Starting By»
También puede tener algún prefijo para los registros en su archivo de texto de entrada que puede ser ignorado durante la carga.
Por ejemplo, en el siguiente archivo employee6.txt, para el 1º, 2º y 5º registro, tenemos «Data:» al principio de la línea. Puede subir sólo estos registros ignorando el prefijo de la línea.
Para ignorar el prefijo de la línea y subir estos registros, (por ejemplo: «Datos:» en el archivo anterior), utilice la opción «líneas que comienzan por» como se muestra a continuación.
LOAD DATA INFILE 'employee6.txt' INTO TABLE employee FIELDS TERMINATED BY ',' LINES STARTING BY 'Data:';
La siguiente es la salida del comando anterior:
Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
Como se ve a continuación, el comando anterior ha subido sólo los registros que empezaban con el prefijo «Data:». Esto es útil para subir selectivamente sólo los registros que tiene cierto prefijo.
Ignorar la línea de cabecera del archivo de carga
En el siguiente archivo de texto de entrada, la primera línea es la línea de cabecera, que tiene el nombre de las columnas.
Durante la carga, queremos ignorar la 1 ª línea de cabecera del archivo employee7.txt. Para ello, utilice la opción IGNORAR 1 líneas como se muestra a continuación.
LOAD DATA INFILE 'employee7.txt' INTO TABLE employee FIELDS TERMINATED BY ',' IGNORE 1 LINES;
Como se ve en la siguiente salida, aunque el archivo de entrada tiene 6 líneas, ignoró la 1ª línea (que es de cabecera) y subió las 5 líneas restantes.
Subir sólo una columna específica (e ignorar otras columnas) del archivo de subida
En el siguiente ejemplo, tenemos valores sólo para tres campos. No tenemos la columna de departamento en este archivo de ejemplo.
# cat employee8.txt 100,Thomas,5000200,Jason,5500300,Mayla,7000400,Nisha,9500500,Randy,6000
Para cargar los valores del registro de entrada a una columna específica en la tabla, especifique los nombres de las columnas durante la carga de datos infile como se muestra a continuación. La última línea del siguiente comando tiene los nombres de las columnas que deben utilizarse para cargar los registros del archivo de texto de entrada.
LOAD DATA INFILE 'employee8.txt' INTO TABLE employee FIELDS TERMINATED BY ',' (id, name, salary);
Como no especificamos la columna «dept» en el comando anterior, veremos que esta columna es NULL como se muestra a continuación.
De nuevo, tenga en cuenta que cuando no especifica la lista de columnas, el comando esperará que todas las columnas estén presentes en el archivo de entrada.
También, si no especifica la lista de columnas en la última línea, obtendrá un error de sintaxis como se muestra a continuación.
Utilizar variables durante la carga con la opción «Set»
Para este ejemplo, vamos a utilizar el siguiente archivo employee2.txt.
En este ejemplo, queremos incrementar el salario en 500 antes de cargarlo en la tabla. Por ejemplo, el salario de Thomas (que es el 1er registro) es 5000. Pero, durante la carga queremos incrementarlo en 500 hasta 5500 y actualizar este valor incrementado en la tabla.
Para ello, utilice el comando SET y use el salario como variable y haga el incremento como se muestra a continuación.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ',' (id, name, dept, @salary) SET salary = @salary+500;
Como se puede ver en la siguiente salida, la columna de salario en incrementado en 500 para todos los registros durante la carga de datos desde el archivo de texto.
Escribir Shell Script para cargar los datos desde el archivo de texto
A veces es posible que desee cargar los datos de un archivo de texto de forma automática sin tener que entrar en mysql prompt cada vez.
Digamos que queremos poner el siguiente comando dentro de un script de shell y ejecutar esto automáticamente en la base de datos thegeekstuff.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ','
Para ejecutar la carga desde la línea de comandos, utilizarás la opción -e en el comando mysql y lo ejecutarás desde el prompt de linux como se muestra a continuación.
# mysql -e "LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ','" \ -u root -pMySQLPassword thegeekstuff
O bien, puedes ponerlo dentro de un script de shell como se muestra a continuación. En este ejemplo, el script de shell load-data.sh tiene el comando mysql anterior.
Da el permiso de ejecución a este script load-data.sh, y ejecútalo desde la línea de comandos, que cargará los datos automáticamente en la tabla. También puede programar esto como cronjob para cargar los datos del archivo automáticamente a la tabla en un intervalo programado.
# chmod u+x load-data.sh# ./load-data.sh
Si te ha gustado este artículo, puede que también te guste..
|
. |
 |
 |
 |
 |