Operaciones de importación y exportación de MySQL

Introducción

En este laboratorio, aprenderá cómo realizar operaciones de importación y exportación de MySQL. Específicamente, explorará cómo importar datos desde un archivo CSV a una tabla de MySQL utilizando la declaración LOAD DATA INFILE. Esto implica crear un archivo CSV llamado employees.csv con datos de empleados, conectarse al servidor MySQL, crear una base de datos y una tabla, y luego utilizar el comando LOAD DATA INFILE para importar los datos.

El laboratorio lo guiará a través del proceso de creación del archivo employees.csv, la conexión al servidor MySQL como usuario root, la creación de la base de datos company y la tabla employees, y finalmente la ejecución de la declaración LOAD DATA INFILE con los parámetros adecuados para importar los datos del archivo CSV a la tabla. El laboratorio también presenta brevemente los componentes de la declaración LOAD DATA INFILE.

Skills Graph

Importar datos CSV con LOAD DATA INFILE

En este paso, aprenderá cómo importar datos desde un archivo CSV a una tabla de MySQL utilizando la declaración LOAD DATA INFILE. Esta es una forma muy eficiente de cargar grandes cantidades de datos en su base de datos.

Antes de comenzar, creemos un simple archivo CSV llamado employees.csv que contenga datos de empleados. Abra una terminal y use nano para crear el archivo:

nano ~/project/employees.csv

Ahora, pegue los siguientes datos en el archivo employees.csv:

1,John,Doe,[email protected],Sales
2,Jane,Smith,[email protected],Marketing
3,Peter,Jones,[email protected],Engineering
4,Mary,Brown,[email protected],HR

Presione Ctrl+X, luego Y y luego Enter para guardar el archivo.

A continuación, necesitamos conectarnos al servidor MySQL. Abra una terminal y ejecute el siguiente comando para conectarse al servidor MySQL como usuario root. Es posible que se le solicite la contraseña de root, que por lo general está en blanco en una configuración predeterminada de la máquina virtual de LabEx.

mysql -u root -p

Si se le solicita una contraseña y no la ha configurado, simplemente presione Enter.

Ahora, creemos una base de datos llamada company y una tabla llamada employees dentro de esa base de datos. Ejecute las siguientes declaraciones SQL en la shell de MySQL:

CREATE DATABASE IF NOT EXISTS company;
USE company;

CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department VARCHAR(50)
);

Ahora que tenemos nuestra base de datos y tabla configuradas, podemos usar la declaración LOAD DATA INFILE para importar los datos del archivo employees.csv. Ejecute la siguiente declaración SQL en la shell de MySQL:

LOAD DATA INFILE '/home/labex/project/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Desglosemos esta declaración:

• LOAD DATA INFILE '/home/labex/project/employees.csv': Especifica la ruta al archivo CSV. Importante: Utilice la ruta absoluta al archivo.
• INTO TABLE employees: Especifica la tabla en la que se importarán los datos.
• FIELDS TERMINATED BY ',': Especifica que los campos en el archivo CSV están separados por comas.
• ENCLOSED BY '"': Especifica que los campos están encerrados entre comillas dobles (si corresponde). En nuestro caso, no es estrictamente necesario, pero es una buena práctica incluirlo.
• LINES TERMINATED BY '\n': Especifica que cada línea en el archivo CSV representa una nueva fila y que las líneas están terminadas por un carácter de nueva línea.
• IGNORE 1 ROWS: Esto es importante si su archivo CSV tiene una fila de encabezado. Le dice a MySQL que omita la primera fila del archivo. Nuestro archivo employees.csv no tiene una fila de encabezado, por lo que debemos eliminar esta línea.

Intentemos la declaración LOAD DATA INFILE de nuevo, esta vez sin la cláusula IGNORE 1 ROWS:

LOAD DATA INFILE '/home/labex/project/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Para verificar que los datos se hayan importado correctamente, ejecute la siguiente declaración SQL:

SELECT * FROM employees;

Debería ver los datos del archivo employees.csv mostrados en la shell de MySQL.

Finalmente, salga de la shell de MySQL:

exit

Exportar el resultado de una consulta a un archivo CSV

En este paso, aprenderá cómo exportar el resultado de una consulta de MySQL a un archivo CSV. Esto es útil para generar informes, compartir datos con otras aplicaciones o hacer copias de seguridad de los datos.

Utilizaremos el cliente de línea de comandos mysql con la opción -e para ejecutar una consulta y redirigir la salida a un archivo. También utilizaremos algunas opciones para formatear la salida como un archivo CSV.

Primero, conectémonos al servidor MySQL. Abra una terminal y ejecute el siguiente comando para conectarse al servidor MySQL como usuario root:

mysql -u root -p

Si se le solicita una contraseña y no la ha configurado, simplemente presione Enter.

Agreguemos más datos a la tabla employees para tener más información que exportar. Ejecute las siguientes declaraciones SQL en la shell de MySQL:

USE company;
INSERT INTO employees (id, first_name, last_name, email, department) VALUES
(5, 'Alice', 'Johnson', '[email protected]', 'Sales'),
(6, 'Bob', 'Williams', '[email protected]', 'Marketing');

Ahora, salga de la shell de MySQL:

exit

Ahora, exportemos todos los datos de la tabla employees a un archivo CSV llamado employees_export.csv en su directorio ~/project. Ejecute el siguiente comando en la terminal:

mysql -u root -p -e "USE company; SELECT * FROM employees;" \
  --batch --raw --skip-column-names \
  | sed 's/\t/","/g; s/^/"/; s/$/"/;' > ~/project/employees_export.csv

Desglosemos este comando:

• mysql -u root -p -e "USE company; SELECT * FROM employees;": Esta parte ejecuta la consulta SQL SELECT * FROM employees en la base de datos company. Se le solicitará la contraseña de root de MySQL.
• --batch: Esta opción le dice a mysql que se ejecute en modo batch, que es adecuado para uso no interactivo.
• --raw: Esta opción le dice a mysql que muestre los datos sin ningún formato.
• --skip-column-names: Esta opción le dice a mysql que omita los nombres de las columnas en la salida.
• sed 's/\t/","/g; s/^/"/; s/$/"/;': Esta parte utiliza el comando sed para formatear la salida como un archivo CSV.
  • s/\t/","/g: Reemplaza las tabulaciones (que son los separadores de campo predeterminados en la salida de mysql) con ",".
  • s/^/"/: Agrega una comilla doble al principio de cada línea.
  • s/$/"/: Agrega una comilla doble al final de cada línea.
• > ~/project/employees_export.csv: Esto redirige la salida del comando a un archivo llamado employees_export.csv en su directorio ~/project.

Ahora, examine el contenido del archivo employees_export.csv. Use nano para abrir el archivo:

nano ~/project/employees_export.csv

Debería ver los datos de la tabla employees, formateados como un archivo CSV, con cada campo encerrado entre comillas dobles y separado por comas.

Presione Ctrl+X para salir de nano.

Solucionar problemas de codificación en las importaciones

En este paso, aprenderá cómo manejar los problemas de codificación al importar datos CSV a MySQL. Los problemas de codificación pueden ocurrir cuando la codificación de caracteres del archivo CSV no coincide con la codificación de caracteres de la tabla de MySQL. Esto puede resultar en la importación de datos garbados o incorrectos.

Primero, creemos un archivo CSV con algunos caracteres especiales que podrían causar problemas de codificación. Usaremos un nombre con un carácter acentuado. Abra una terminal y use nano para crear el archivo:

nano ~/project/employees_encoding.csv

Ahora, pegue los siguientes datos en el archivo employees_encoding.csv:

1,René,Doe,[email protected],Sales
2,Jane,Smith,[email protected],Marketing

Presione Ctrl+X, luego Y y luego Enter para guardar el archivo.

Ahora, intentemos importar estos datos a nuestra tabla employees sin especificar ninguna codificación. Primero, necesitamos borrar los datos existentes de la tabla. Conéctese al servidor MySQL:

mysql -u root -p

Si se le solicita una contraseña y no la ha configurado, simplemente presione Enter.

Ejecute las siguientes declaraciones SQL en la shell de MySQL:

USE company;
TRUNCATE TABLE employees;

Esto eliminará todas las filas existentes de la tabla employees.

Ahora, salga de la shell de MySQL:

exit

Ahora, intentemos importar el archivo employees_encoding.csv usando el mismo comando LOAD DATA INFILE que usamos antes:

mysql -u root -p -e "USE company; LOAD DATA INFILE '/home/labex/project/employees_encoding.csv' INTO TABLE employees FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"

Después de ingresar su contraseña, conéctese al servidor MySQL nuevamente:

mysql -u root -p

Y verifique los datos:

USE company;
SELECT * FROM employees;

Es posible que vea que el first_name del primer empleado no se muestra correctamente. Esto se debe a un problema de codificación. Salga de la shell de MySQL:

exit

Para solucionar esto, necesitamos especificar la codificación de caracteres del archivo CSV cuando usamos LOAD DATA INFILE. Una codificación común es utf8. Modifique la declaración LOAD DATA INFILE para incluir la cláusula CHARACTER SET utf8:

mysql -u root -p -e "USE company; LOAD DATA INFILE '/home/labex/project/employees_encoding.csv' INTO TABLE employees CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"

Ahora, conéctese al servidor MySQL nuevamente:

mysql -u root -p

Y verifique los datos:

USE company;
SELECT * FROM employees;

Ahora debería ver que el first_name del primer empleado se muestra correctamente como "René".

Finalmente, salga de la shell de MySQL:

exit

En este ejemplo, usamos utf8. Sin embargo, es posible que deba usar un juego de caracteres diferente según la codificación de su archivo CSV. Las codificaciones comunes incluyen latin1, utf8mb4 y gbk.

Validar los datos importados

En este paso, aprenderá cómo validar los datos que se han importado a su tabla de MySQL. La validación de datos es un paso importante para garantizar la precisión e integridad de sus datos. Cubriremos algunas técnicas básicas de validación utilizando consultas SQL.

Primero, agreguemos algunos datos potencialmente problemáticos a nuestra tabla employees. Abra una terminal y use nano para crear el archivo:

nano ~/project/employees_validation.csv

Ahora, pegue los siguientes datos en el archivo employees_validation.csv. Tenga en cuenta que la última fila tiene un formato de correo electrónico no válido y falta el departamento:

3,Invalid,Email,invalid_email,Sales
4,Missing,Department,[email protected],

Presione Ctrl+X, luego Y y luego Enter para guardar el archivo.

Ahora, importemos estos datos a nuestra tabla employees. Conéctese al servidor MySQL:

mysql -u root -p

Si se le solicita una contraseña y no la ha configurado, simplemente presione Enter.

Ejecute las siguientes declaraciones SQL en la shell de MySQL:

USE company;
LOAD DATA INFILE '/home/labex/project/employees_validation.csv'
INTO TABLE employees
CHARACTER SET utf8
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Ahora que hemos importado los datos, realicemos algunas comprobaciones de validación.

1. Comprobar formatos de correo electrónico no válidos:

Podemos usar una expresión regular para comprobar formatos de correo electrónico no válidos. Ejecute la siguiente consulta SQL:

SELECT * FROM employees WHERE email NOT LIKE '%@%.%';

Esta consulta devolverá cualquier fila en la que la columna email no contenga los caracteres @ y ., que son esenciales para una dirección de correo electrónico válida. Debería ver la fila con invalid_email en el resultado.

2. Comprobar departamentos faltantes:

Podemos comprobar los departamentos faltantes buscando cadenas vacías en la columna department. Ejecute la siguiente consulta SQL:

SELECT * FROM employees WHERE department = '';

Esta consulta devolverá cualquier fila en la que la columna department esté vacía. Debería ver la fila con el departamento faltante en el resultado.

3. Comprobar IDs duplicados:

Aunque no hemos introducido IDs duplicados en este ejemplo, es una comprobación de validación común. Puede usar la siguiente consulta para encontrar IDs duplicados:

SELECT id, COUNT(*) FROM employees GROUP BY id HAVING COUNT(*) > 1;

Esta consulta devolverá cualquier ID que aparezca más de una vez en la tabla employees.

4. Comprobar coincidencias de tipos de datos:

MySQL generalmente manejará las coincidencias de tipos de datos durante el proceso de importación, pero aún es una buena idea comprobar. Por ejemplo, si tiene una columna numérica que solo debe contener valores positivos, puede comprobar valores negativos.

Estos son solo algunos ejemplos de cómo puede validar los datos importados utilizando consultas SQL. Las comprobaciones de validación específicas que debe realizar dependerán de la estructura y el contenido de sus datos.

Finalmente, salga de la shell de MySQL:

exit

Resumen

En este laboratorio (lab), aprendiste cómo importar datos de un archivo CSV a una tabla de MySQL utilizando la declaración LOAD DATA INFILE. Esto implicó crear un archivo CSV (employees.csv) con datos de empleados, conectarse al servidor de MySQL, crear una base de datos (company) y una tabla (employees), y luego utilizar el comando LOAD DATA INFILE para importar los datos. El comando especifica la ruta del archivo, el nombre de la tabla, los terminadores de campo y línea, y el número de filas a ignorar.

El laboratorio también destacó la importancia de especificar la ruta correcta del archivo y comprender las diferentes cláusulas dentro de la declaración LOAD DATA INFILE para garantizar una importación exitosa de los datos.