MySQL Import and Export Operations

Introduction

In this lab, you will learn the essential skills of importing and exporting data in a MySQL database. You will practice loading data from a CSV (Comma-Separated Values) file into a table using the LOAD DATA INFILE command, a fast and efficient method for bulk data insertion.

You will also learn the reverse process: exporting data from a table into a new CSV file. Additionally, the lab covers performing basic data validation checks after an import to ensure data quality. By the end of this lab, you will be proficient in moving data in and out of MySQL.

Prepare the Database and Table

Before you can import data, you need a destination for it. This involves creating a database to house your data and a table with a structure that matches the data you intend to import.

First, open the terminal from your desktop.

Connect to the MySQL server as the root user. In this lab environment, you can use sudo to connect without a password.

sudo mysql -u root

Once connected, you will see the MySQL prompt (mysql>), which indicates you are now interacting directly with the database server.

Next, create a new database named company. The IF NOT EXISTS clause is a good practice that prevents an error if the database has already been created.

CREATE DATABASE IF NOT EXISTS company;

Now, switch to your newly created database so that all subsequent commands apply to it.

USE company;

Finally, create a table named employees to store the employee data. The table structure must match the columns in the CSV file you will import later.

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

• INT PRIMARY KEY: Defines the id column as an integer and a primary key, which means each value must be unique.
• VARCHAR(50): Defines a column that can store a variable-length string up to 50 characters.

You can verify that the table was created successfully by running:

SHOW TABLES;

You should see the employees table listed in the output.

+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)

Keep the MySQL shell open, as you will continue to use it in the next step.

Import Data from a CSV File

With the database and table ready, you can now import data from an external file. The LOAD DATA INFILE statement is a highly efficient way to bulk-load data from a text file into a table.

The setup script for this lab has already created a file named employees.csv in the /tmp directory. Before importing, it's good practice to inspect the file's contents.

Important: You will need to open a new terminal tab for this command, as your current terminal is running the MySQL shell. Click the + icon in the terminal window to open a new tab. In the new terminal, run:

cat /tmp/employees.csv

The output shows four lines of comma-separated data:

1,John,Doe,john.doe@example.com,Sales
2,Jane,Smith,jane.smith@example.com,Marketing
3,Peter,Jones,peter.jones@example.com,Engineering
4,Mary,Brown,mary.brown@example.com,HR

Now, switch back to your original terminal tab with the MySQL shell (mysql>). Use the LOAD DATA INFILE command to import the file.

LOAD DATA INFILE '/tmp/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Let's break down this command:

• LOAD DATA INFILE '/tmp/employees.csv': Specifies the full, absolute path to the source file.
• INTO TABLE employees: Specifies the target table for the data.
• FIELDS TERMINATED BY ',': Tells MySQL that commas separate the data fields (columns) in each row.
• LINES TERMINATED BY '\n': Tells MySQL that each new line in the file represents a new row.

After the command executes, MySQL will report the number of rows imported. To verify the import was successful, query the table to see its contents.

SELECT * FROM employees;

The output should display the four records from the CSV file, now stored in your employees table.

+----+------------+-----------+---------------------------+-------------+
| id | first_name | last_name | email                     | department  |
+----+------------+-----------+---------------------------+-------------+
|  1 | John       | Doe       | john.doe@example.com      | Sales       |
|  2 | Jane       | Smith     | jane.smith@example.com    | Marketing   |
|  3 | Peter      | Jones     | peter.jones@example.com   | Engineering |
|  4 | Mary       | Brown     | mary.brown@example.com    | HR          |
+----+------------+-----------+---------------------------+-------------+
4 rows in set (0.00 sec)

Export Query Results to a CSV File

Exporting data is just as important as importing. You might need to create reports, share data with other systems, or perform analysis in a spreadsheet program. The SELECT ... INTO OUTFILE statement allows you to save the result of any query directly to a file.

First, let's add two more employees to the table in your MySQL shell.

INSERT INTO employees (id, first_name, last_name, email, department) VALUES
(5, 'Alice', 'Johnson', 'alice.johnson@example.com', 'Sales'),
(6, 'Bob', 'Williams', 'bob.williams@example.com', 'Marketing');

Now, export the entire employees table to a new file named employees_export.csv. First, make sure you're still in the correct database:

SELECT id, first_name, last_name, email, department
FROM company.employees
INTO OUTFILE '/tmp/employees_export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

• SELECT ...: This is a standard query that specifies which data to export.
• INTO OUTFILE '/tmp/employees_export.csv': Specifies the full path for the output file. For security, MySQL requires that this file does not already exist.
• FIELDS TERMINATED BY ',': Separates fields with a comma.
• ENCLOSED BY '"': Wraps each field value in double quotes, which is a common CSV format.
• LINES TERMINATED BY '\n': Ends each row with a newline character.

After running the command, switch to your other terminal tab (or open a new one) and view the contents of the newly created file.

cat /tmp/employees_export.csv

You will see all six rows from your table, formatted as a CSV file.

"1","John","Doe","john.doe@example.com","Sales"
"2","Jane","Smith","jane.smith@example.com","Marketing"
"3","Peter","Jones","peter.jones@example.com","Engineering"
"4","Mary","Brown","mary.brown@example.com","HR"
"5","Alice","Johnson","alice.johnson@example.com","Sales"
"6","Bob","Williams","bob.williams@example.com","Marketing"

Validate Imported Data

After importing data, it is crucial to validate it to ensure quality and integrity. Real-world data is often messy, containing errors, missing values, or incorrect formats. This step shows you how to use simple SQL queries to find common problems.

The setup script created employees_validation.csv, which contains an invalid email and a missing department value. First, clear the employees table in your MySQL shell.

TRUNCATE TABLE employees;

Now, import the validation file.

LOAD DATA INFILE '/tmp/employees_validation.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

With the "dirty" data loaded, let's run some validation checks.

1. Find Invalid Email Formats

A very basic check for a valid email is to see if it contains an @ symbol and a . symbol. We can use NOT LIKE to find rows that fail this check.

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

This query will find the row where the email is invalid_email, as it lacks the required symbols.

+----+------------+-----------+---------------+------------+
| id | first_name | last_name | email         | department |
+----+------------+-----------+---------------+------------+
|  3 | Invalid    | Email     | invalid_email | Sales      |
+----+------------+-----------+---------------+------------+
1 row in set (0.00 sec)

2. Find Missing Departments

You can find rows where a value is missing by checking for an empty string ''.

SELECT * FROM employees WHERE department = '';

This query will find the row where the department was left blank in the CSV file.

+----+------------+------------+--------------------------------+------------+
| id | first_name | last_name  | email                          | department |
+----+------------+------------+--------------------------------+------------+
|  4 | Missing    | Department | missing.department@example.com |            |
+----+------------+------------+--------------------------------+------------+
1 row in set (0.00 sec)

These simple queries are powerful tools for a first-pass data quality check. After identifying problematic rows, you can decide whether to fix them with UPDATE statements or remove them with DELETE.

You have now completed the lab. You can exit the MySQL shell.

exit

Summary

In this lab, you have learned the fundamental operations for moving data into and out of a MySQL database. You started by setting up a proper database environment with a new database and table. You then used the LOAD DATA INFILE command to efficiently import data from a CSV file.

Next, you practiced exporting data from a table to a new CSV file using the SELECT ... INTO OUTFILE statement, a common task for reporting and data sharing. Finally, you learned how to perform basic data validation with SQL queries to check for formatting errors and missing values after an import. These skills are essential for any developer or administrator working with MySQL.