MySQL Import and Export Operations

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 named employees.csv 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.

The lab will guide you through the process of creating the employees.csv file, connecting to the MySQL server as the root user, creating the company database and the employees table, and finally executing the LOAD DATA INFILE statement with appropriate parameters to import the data from the CSV file into the table. The lab also briefly introduces the components of the LOAD DATA INFILE statement.

Skills Graph

Import CSV Data with LOAD DATA INFILE

In this step, you will learn how to import data from a CSV file into a MySQL table using the LOAD DATA INFILE statement. This is a very efficient way to load large amounts of data into your database.

Before we begin, let's create a simple CSV file named employees.csv containing employee data. Open a terminal and use nano to create the file:

nano ~/project/employees.csv

Now, paste the following data into the employees.csv file:

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

Press Ctrl+X, then Y, and then Enter to save the file.

Next, we need to connect to the MySQL server. Open a terminal and execute the following command to connect to the MySQL server as the root user. You may be prompted for the root password, which is usually blank in a default LabEx VM setup.

mysql -u root -p

If prompted for a password, and you haven't set one, just press Enter.

Now, let's create a database named company and a table named employees within that database. Execute the following SQL statements in the MySQL shell:

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

Now that we have our database and table set up, we can use the LOAD DATA INFILE statement to import the data from the employees.csv file. Execute the following SQL statement in the MySQL shell:

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

Let's break down this statement:

• LOAD DATA INFILE '/home/labex/project/employees.csv': Specifies the path to the CSV file. Important: Use the absolute path to the file.
• INTO TABLE employees: Specifies the table to import the data into.
• FIELDS TERMINATED BY ',': Specifies that the fields in the CSV file are separated by commas.
• ENCLOSED BY '"': Specifies that fields are enclosed by double quotes (if applicable). In our case, it's not strictly necessary, but it's good practice to include it.
• LINES TERMINATED BY '\n': Specifies that each line in the CSV file represents a new row, and lines are terminated by a newline character.
• IGNORE 1 ROWS: This is important if your CSV file has a header row. It tells MySQL to skip the first row of the file. Our employees.csv file does not have a header row, so we should remove this line.

Let's try the LOAD DATA INFILE statement again, this time without the IGNORE 1 ROWS clause:

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

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

SELECT * FROM employees;

You should see the data from the employees.csv file displayed in the MySQL shell.

Finally, exit the MySQL shell:

exit

Export a Query Result to CSV

In this step, you will learn how to export the result of a MySQL query to a CSV file. This is useful for generating reports, sharing data with other applications, or backing up data.

We'll use the mysql command-line client with the -e option to execute a query and redirect the output to a file. We'll also use some options to format the output as a CSV file.

First, let's connect to the MySQL server. Open a terminal and execute the following command to connect to the MySQL server as the root user:

mysql -u root -p

If prompted for a password, and you haven't set one, just press Enter.

Let's add some more data to the employees table so we have more to export. Execute the following SQL statements in the MySQL shell:

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

Now, exit the MySQL shell:

exit

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 command in the 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

Let's break down this command:

• mysql -u root -p -e "USE company; SELECT * FROM employees;": This part executes the SQL query SELECT * FROM employees against the company database. You'll be prompted for the MySQL root password.
• --batch: This option tells mysql to run in batch mode, which is suitable for non-interactive use.
• --raw: This option tells mysql to output the data without any formatting.
• --skip-column-names: This option tells mysql to skip the column names in the output.
• sed 's/\t/","/g; s/^/"/; s/$/"/;': This part uses the sed command to format the output as a CSV file.
  • s/\t/","/g: Replaces tabs (which are the default field separators in mysql output) with ",".
  • s/^/"/: Adds a double quote at the beginning of each line.
  • s/$/"/: Adds a double quote at the end of each line.
• > ~/project/employees_export.csv: This redirects the output of the command to a file named employees_export.csv in your ~/project directory.

Now, let's examine the contents of the employees_export.csv file. Use nano to open the file:

nano ~/project/employees_export.csv

You should see the data from the employees table, formatted as a CSV file, with each field enclosed in double quotes and separated by commas.

Press Ctrl+X to exit nano.

Fix Encoding Issues in Imports

In this step, you will learn how to handle encoding issues when importing CSV data into MySQL. Encoding issues can occur when the character encoding of the CSV file does not match the character encoding of the MySQL table. This can result in garbled or incorrect data being imported.

First, let's create a CSV file with some special characters that might cause encoding problems. We'll use a name with an accented character. Open a terminal and use nano to create the file:

nano ~/project/employees_encoding.csv

Now, paste the following data into the employees_encoding.csv file:

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

Press Ctrl+X, then Y, and then Enter to save the file.

Now, let's try to import this data into our employees table without specifying any encoding. First, we need to clear the existing data from the table. Connect to the MySQL server:

mysql -u root -p

If prompted for a password, and you haven't set one, just press Enter.

Execute the following SQL statements in the MySQL shell:

USE company;
TRUNCATE TABLE employees;

This will remove all existing rows from the employees table.

Now, exit the MySQL shell:

exit

Now, let's try importing the employees_encoding.csv file using the same LOAD DATA INFILE command we used before:

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';"

After entering your password, connect to the MySQL server again:

mysql -u root -p

And check the data:

USE company;
SELECT * FROM employees;

You might see that the first_name for the first employee is not displayed correctly. This is because of an encoding issue. Exit the MySQL shell:

exit

To fix this, we need to specify the character encoding of the CSV file when using LOAD DATA INFILE. A common encoding is utf8. Modify the LOAD DATA INFILE statement to include the CHARACTER SET utf8 clause:

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';"

Now, connect to the MySQL server again:

mysql -u root -p

And check the data:

USE company;
SELECT * FROM employees;

You should now see that the first_name for the first employee is displayed correctly as "René".

Finally, exit the MySQL shell:

exit

In this example, we used utf8. However, you might need to use a different character set depending on the encoding of your CSV file. Common encodings include latin1, utf8mb4, and gbk.

Validate Imported Data

In this step, you will learn how to validate the data that has been imported into your MySQL table. Data validation is an important step to ensure the accuracy and integrity of your data. We'll cover some basic validation techniques using SQL queries.

First, let's add some potentially problematic data to our employees table. Open a terminal and use nano to create the file:

nano ~/project/employees_validation.csv

Now, paste the following data into the employees_validation.csv file. Note that the last row has an invalid email format and a missing department:

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

Press Ctrl+X, then Y, and then Enter to save the file.

Now, let's import this data into our employees table. Connect to the MySQL server:

mysql -u root -p

If prompted for a password, and you haven't set one, just press Enter.

Execute the following SQL statements in the MySQL shell:

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

Now that we've imported the data, let's perform some validation checks.

1. Check for Invalid Email Formats:

We can use a regular expression to check for invalid email formats. Execute the following SQL query:

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

This query will return any rows where the email column does not contain the @ and . characters, which are essential for a valid email address. You should see the row with invalid_email in the result.

2. Check for Missing Departments:

We can check for missing departments by looking for empty strings in the department column. Execute the following SQL query:

SELECT * FROM employees WHERE department = '';

This query will return any rows where the department column is empty. You should see the row with the missing department in the result.

3. Check for Duplicate IDs:

Although we haven't introduced duplicate IDs in this example, it's a common validation check. You can use the following query to find duplicate IDs:

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

This query will return any IDs that appear more than once in the employees table.

4. Check for Data Type Mismatches:

MySQL will usually handle data type mismatches during the import process, but it's still a good idea to check. For example, if you have a numeric column that should only contain positive values, you can check for negative values.

These are just a few examples of how you can validate imported data using SQL queries. The specific validation checks you need to perform will depend on the structure and content of your data.

Finally, exit the MySQL shell:

exit

Summary

In this lab, you learned how to import data from a CSV file into a MySQL table using the LOAD DATA INFILE statement. This involved creating a CSV file (employees.csv) with employee data, connecting to the MySQL server, creating a database (company) and a table (employees), and then using the LOAD DATA INFILE command to import the data. The command specifies the file path, table name, field and line terminators, and the number of rows to ignore.

The lab also highlighted the importance of specifying the correct file path and understanding the different clauses within the LOAD DATA INFILE statement to ensure successful data import.