MySQL CSV Import Export

In modern data-driven applications, the ability to move data in and out of your database is a crucial skill. CSV (Comma-Separated Values) format is one of the most common and versatile formats for data exchange. In this tutorial, we'll explore how to import data from CSV files into MySQL and how to export MySQL data into CSV files.

Understanding CSV Format

CSV files are plain text files that store tabular data where each line represents a row, and values within a row are separated by commas (or other delimiters).

A simple CSV file might look like this:

id,name,email,join_date
1,John Smith,[email protected],2023-01-15
2,Sarah Johnson,[email protected],2023-02-20
3,Ahmed Khan,[email protected],2023-03-05

Importing CSV Data into MySQL

MySQL provides several methods to import CSV data:

Method 1: Using LOAD DATA INFILE

The LOAD DATA INFILE statement is the most efficient way to import large CSV files into MySQL.

Basic Syntax

sql

LOAD DATA INFILE 'file_path.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Example

Let's say we have a CSV file called users.csv with the following content:

id,name,email,join_date
1,John Smith,[email protected],2023-01-15
2,Sarah Johnson,[email protected],2023-02-20
3,Ahmed Khan,[email protected],2023-03-05

First, create a table to store this data:

sql

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    join_date DATE
);

Then import the CSV file:

sql

LOAD DATA INFILE 'C:/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

The IGNORE 1 ROWS clause skips the header row containing column names.

Method 2: Using MySQL Workbench

For those who prefer graphical interfaces:

1. Open MySQL Workbench
2. Connect to your database
3. Right-click on your table
4. Select "Table Data Import Wizard"
5. Follow the wizard's instructions to select your CSV file and map columns

Common Import Challenges

Security Restrictions

When using LOAD DATA INFILE, you might encounter this error:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

To resolve this:

1. Check the secure-file-priv setting:

sql

SHOW VARIABLES LIKE 'secure_file_priv';

2. Place your CSV file in the allowed directory or modify the MySQL configuration.

Alternatively, use LOAD DATA LOCAL INFILE to load from the client side:

sql

LOAD DATA LOCAL INFILE 'C:/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Dealing with Different Delimiters

If your CSV uses tab or other delimiters:

sql

LOAD DATA INFILE 'C:/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY '\t' -- Tab delimiter
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Handling Date Formats

For special date formats, use the SET clause:

sql

LOAD DATA INFILE 'C:/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, name, email, @join_date)
SET join_date = STR_TO_DATE(@join_date, '%m/%d/%Y');

Exporting MySQL Data to CSV

Method 1: Using SELECT ... INTO OUTFILE

This is the fastest way to export data directly from MySQL:

sql

SELECT * FROM users
INTO OUTFILE 'C:/allowed_directory/exported_users.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Example with Column Headers

To include column headers in your export:

sql

(SELECT 'id', 'name', 'email', 'join_date')
UNION
(SELECT * FROM users)
INTO OUTFILE 'C:/allowed_directory/exported_users.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Method 2: Using mysqldump

The mysqldump command-line utility is useful for exporting entire tables or databases:

bash

mysqldump -u username -p --tab=C:/allowed_directory --fields-terminated-by=, database_name users

Method 3: Using MySQL Workbench

1. Open MySQL Workbench
2. Connect to your database
3. Run a SELECT query on your table
4. Right-click on the result grid
5. Select "Export Recordset to an External File"
6. Choose CSV format and configure options

Real-World Examples

Example 1: Importing Customer Data for a New E-commerce System

Imagine you're migrating customer data from an old system to a new MySQL-based e-commerce platform.

1. Export the customer data from the old system as customers.csv:

customer_id,first_name,last_name,email,phone,address,city,postal_code
101,Maria,Garcia,[email protected],555-1234,"123 Main St",Austin,78701
102,Robert,Johnson,[email protected],555-5678,"456 Elm St",Dallas,75201
103,Lisa,Wong,[email protected],555-9012,"789 Pine Ave",Houston,77002

2. Create your customers table:

sql

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(15),
    address VARCHAR(200),
    city VARCHAR(50),
    postal_code VARCHAR(10)
);

3. Import the CSV data:

sql

LOAD DATA INFILE 'C:/path/to/customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Example 2: Exporting Sales Data for Analytics

Suppose you need to export monthly sales data for analysis in Excel or another tool.

1. Create a SQL query to gather the data:

sql

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    product_id,
    product_name,
    SUM(quantity) AS total_quantity,
    SUM(price * quantity) AS total_sales
FROM 
    orders
JOIN 
    order_items USING (order_id)
JOIN 
    products USING (product_id)
WHERE 
    order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY 
    DATE_FORMAT(order_date, '%Y-%m'), product_id, product_name
ORDER BY 
    month, total_sales DESC;

2. Export the result to CSV:

sql

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    product_id,
    product_name,
    SUM(quantity) AS total_quantity,
    SUM(price * quantity) AS total_sales
FROM 
    orders
JOIN 
    order_items USING (order_id)
JOIN 
    products USING (product_id)
WHERE 
    order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY 
    DATE_FORMAT(order_date, '%Y-%m'), product_id, product_name
ORDER BY 
    month, total_sales DESC
INTO OUTFILE 'C:/allowed_directory/monthly_sales_2023.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Performance Considerations

When dealing with large datasets, consider these tips:

1. Disable foreign key checks during import:

   sql

   SET FOREIGN_KEY_CHECKS = 0;
   -- Import data
   SET FOREIGN_KEY_CHECKS = 1;

2. Disable indexes before import:

   sql

   ALTER TABLE your_table DISABLE KEYS;
   -- Import data
   ALTER TABLE your_table ENABLE KEYS;

3. Use extended inserts for better performance when programmatically creating SQL to import data.

4. Increase buffer sizes for large imports:

   sql

   SET GLOBAL net_buffer_length = 1000000;
   SET GLOBAL max_allowed_packet = 1000000000;

Common Error Solutions

Error 1148: The used command is not allowed with this MySQL version

This happens when trying to use LOCAL keyword without server support.

Solution:

1. Enable local_infile in MySQL server:
   sql

   SET GLOBAL local_infile = 1;

2. For MySQL client, start it with:
   bash

   mysql --local-infile=1 -u username -p

Error 1290: Secure file priv

Use the directory identified by:

sql

SHOW VARIABLES LIKE 'secure_file_priv';

Summary

In this tutorial, we covered:

• Importing CSV data into MySQL using LOAD DATA INFILE and MySQL Workbench
• Exporting MySQL data to CSV using SELECT INTO OUTFILE and other methods
• Real-world examples demonstrating practical applications
• Performance considerations for handling large datasets
• Common error solutions

CSV import and export capabilities are essential skills for effective data management in MySQL. They enable seamless data migration, backup, analysis, and integration with other systems and tools.

Practice Exercises

1. Create a CSV file with employee data and import it into a MySQL table.
2. Export a subset of your database (e.g., all customers from a specific city) to a CSV file.
3. Import a CSV file with a different delimiter (e.g., semicolons or tabs).
4. Create a script that exports the current day's transaction data to a CSV file.

Additional Resources

• MySQL Documentation: LOAD DATA INFILE
• MySQL Documentation: SELECT ... INTO Syntax
• MySQL Documentation: mysqldump