Opérations d'importation et d'exportation MySQL

Introduction

In this lab, you will learn how to perform MySQL import and export operations. Specifically, you will explore importing data from a CSV file into a MySQL table using the LOAD DATA INFILE statement. This involves creating a CSV file with employee data, connecting to the MySQL server, creating a database and table, and then using the LOAD DATA INFILE command to import the data.

You will also learn how to export data from a MySQL table to a CSV file, handle potential encoding issues during import, and perform basic data validation after importing. Mastering these operations is crucial for managing data effectively in MySQL.

Skills Graph

Prepare Database and Table

Before we can import data, we need a database and a table to store it. In this step, you will connect to the MySQL server and create a new database and a table with the appropriate structure for our employee data.

Open a terminal and connect to the MySQL server as the root user. You will be prompted for the root password. In this LabEx environment, the root password is blank, so just press Enter.

sudo mysql -u root

Once you are in the MySQL shell, execute the following SQL statements to create a database named company and a table named employees within that database:

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)
);

The CREATE DATABASE IF NOT EXISTS company; statement creates the company database if it doesn't already exist. The USE company; statement selects the company database as the current database for subsequent operations.

The CREATE TABLE IF NOT EXISTS employees (...) statement creates the employees table if it doesn't already exist. It defines five columns: id (an integer and the primary key), first_name, last_name, email, and department (all variable-length strings).

You can verify that the database and table were created by executing the following commands in the MySQL shell:

SHOW DATABASES;
SHOW TABLES;
DESCRIBE employees;

You should see company in the list of databases, employees in the list of tables within the company database, and the structure of the employees table.

Keep the MySQL shell open for the next step.

Import Data with LOAD DATA INFILE

Now that we have our database and table set up, we can import data from a CSV file. We will use the LOAD DATA INFILE statement, which is a powerful and efficient way to load data from a file into a table.

We have already created a CSV file named employees.csv in your ~/project directory during the setup. Let's examine its content using the cat command in a new terminal tab (keep the MySQL shell open in the first tab):

cat ~/project/employees.csv

You should see the following output:

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

This file contains four rows of employee data, with values separated by commas.

Now, switch back to the terminal with the MySQL shell. Execute the following LOAD DATA INFILE statement to import the data from employees.csv into the employees table:

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

Let's understand the different parts of this statement:

• LOAD DATA INFILE '/home/labex/project/employees.csv': This specifies the path to the CSV file you want to import. It's important to use the absolute path to the file.
• INTO TABLE employees: This indicates the target table where the data will be loaded.
• FIELDS TERMINATED BY ',': This tells MySQL that the fields (columns) in the CSV file are separated by commas.
• ENCLOSED BY '"': This specifies that fields might be optionally enclosed by double quotes. While our current employees.csv doesn't use quotes, it's a common practice to include this for robustness.
• LINES TERMINATED BY '\n': This indicates that each line in the CSV file represents a new row and is terminated by a newline character.

After executing the LOAD DATA INFILE statement, you should see a message indicating the number of rows affected.

To verify that the data has been imported correctly, execute the following SQL statement in the MySQL shell:

SELECT * FROM employees;

You should see the four rows from the employees.csv file displayed in the output.

Keep the MySQL shell open for the next step.

Export Query Results to CSV

Exporting data from your database to a CSV file is a common task for reporting, analysis, or sharing data. In this step, you will learn how to export the result of a SQL query to a CSV file using the SELECT ... INTO OUTFILE statement.

First, let's add a couple more rows to our employees table so we have more data to export. In the MySQL shell, execute the following INSERT statements:

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

Now, let's export all the data from the employees table to a CSV file named employees_export.csv in your ~/project directory. Execute the following SELECT ... INTO OUTFILE statement in the MySQL shell:

SELECT id, first_name, last_name, email, department
INTO OUTFILE '/home/labex/project/employees_export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Let's break down this statement:

• SELECT id, first_name, last_name, email, department: This specifies the columns you want to export. You can select specific columns or use * to select all columns.
• INTO OUTFILE '/home/labex/project/employees_export.csv': This specifies the path to the output file. The file must not already exist, and the MySQL server process must have write permissions to the directory. The /home/labex/project directory is writable by the mysql user in this environment.
• FIELDS TERMINATED BY ',': This specifies that the fields in the output file should be separated by commas.
• ENCLOSED BY '"': This specifies that the fields in the output file should be enclosed by double quotes.
• LINES TERMINATED BY '\n': This indicates that each row in the output file should be terminated by a newline character.

After executing the statement, you should see a message indicating the number of rows exported.

Now, open a new terminal tab and examine the contents of the employees_export.csv file using the cat command:

cat ~/project/employees_export.csv

You should see all six rows of employee data, formatted as a CSV file with fields enclosed in double quotes and separated by commas.

Keep the MySQL shell open for the next step.

Handle Encoding Issues

Character encoding issues can arise when importing data, especially if the CSV file contains characters that are not supported by the default character set of your MySQL table. In this step, you will learn how to handle such issues by specifying the character set during the import process.

We have created a CSV file named employees_encoding.csv in your ~/project directory that contains a name with an accented character (René). Let's examine its content:

cat ~/project/employees_encoding.csv

You should see:

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

Now, let's clear the existing data from the employees table so we can import this new data. In the MySQL shell, execute:

TRUNCATE TABLE employees;

This command removes all rows from the employees table.

Now, let's try to import employees_encoding.csv using the LOAD DATA INFILE statement without specifying the character set:

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

After the import, let's check the data:

SELECT * FROM employees;

You might notice that the name "René" is not displayed correctly, possibly appearing as "René" or similar garbled characters. This indicates an encoding issue.

To fix this, we need to specify the character set of the CSV file during the import. The employees_encoding.csv file is encoded in UTF-8, which is a common and widely supported character set.

First, clear the table again:

TRUNCATE TABLE employees;

Now, import the data again, this time including the CHARACTER SET utf8 clause:

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

After the import, check the data again:

SELECT * FROM employees;

You should now see "René" displayed correctly. By specifying CHARACTER SET utf8, we told MySQL to interpret the data in the CSV file using the UTF-8 encoding, resolving the encoding issue.

Keep the MySQL shell open for the next step.

Validate Imported Data

After importing data, it's crucial to validate it to ensure its accuracy and integrity. In this step, you will learn some basic SQL queries to check for common data issues after importing.

We have created a CSV file named employees_validation.csv in your ~/project directory that contains some potentially problematic data, including an invalid email format and a missing department. Let's examine its content:

cat ~/project/employees_validation.csv

You should see:

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

Notice the row with ID 3 has an email invalid_email which is not a valid format, and the row with ID 4 has an empty string for the department.

First, clear the existing data from the employees table:

TRUNCATE TABLE employees;

Now, import the data from employees_validation.csv:

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

Now that the data is imported, let's perform some validation checks using SQL queries in the MySQL shell.

1. Check for Invalid Email Formats:

We can use the LIKE operator to check for email addresses that don't contain both @ and . characters, which are typically required for a valid email format.

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

This query should return the row with id = 3 because its email address invalid_email does not contain @ and ..

2. Check for Missing Departments:

We can check for rows where the department column is an empty string.

SELECT * FROM employees WHERE department = '';

This query should return the row with id = 4 because its department is an empty string.

3. Check for Duplicate IDs:

Although our validation file doesn't have duplicate IDs, it's a common validation check. You can use GROUP BY and HAVING to find duplicate primary keys.

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

This query would return any id values that appear more than once in the table. In this case, it should return an empty result set.

These are just a few examples of basic data validation using SQL. Depending on your data and requirements, you might need more complex checks using regular expressions, constraints, or stored procedures.

Finally, you can exit the MySQL shell:

exit

You have successfully learned how to import and export data in MySQL, handle encoding issues, and perform basic data validation.

Summary

In this lab, you have learned how to perform essential MySQL import and export operations. You started by preparing your database environment by creating a database and a table. You then successfully imported data from a CSV file into your table using the LOAD DATA INFILE statement, understanding its various clauses for specifying field and line terminators.

You also learned how to export data from a MySQL table to a CSV file using the SELECT ... INTO OUTFILE statement, which is useful for generating reports or transferring data. Furthermore, you explored how to address potential encoding issues during import by specifying the character set of the source file. Finally, you practiced validating imported data using SQL queries to identify common issues like invalid email formats and missing values.

Mastering these import and export techniques is fundamental for managing data effectively in MySQL databases.