The World of MySQL & MariaDB

How to Import CSV Files into MySQL: Step-by-Step Guide with Examples

SQL Basics
目次

1. Introduction to Importing CSV Files into MySQL

Importing CSV files into MySQL is a powerful way to streamline data management and eliminate the need for manual data entry. For example, when you want to bulk load information collected from multiple data sources into a database, or automatically process data from an external system, CSV import becomes extremely useful.

Common Use Cases for CSV Import

  • Handling large datasets: Instead of manually entering thousands of records, you can quickly process them using CSV import.
  • Data migration: Convenient for importing data exported from other systems into MySQL.
  • Regular data updates: In systems that require frequent updates, automation with CSV files is highly effective.

2. Prerequisites

Before importing a CSV file into MySQL, certain configurations and preparations are required to ensure a smooth process.

2.1 Required Environment

  • MySQL server installation
    You need to have MySQL properly installed. Verify that MySQL is running on your environment—whether Windows, macOS, or Linux.
  • Verify necessary permissions
    To import CSV files, the MySQL user must have the appropriate privileges, especially the LOAD DATA INFILE privilege. Without it, imports may be denied.

2.2 CSV File Format

  • Delimiter
    CSV files are usually comma-delimited, but sometimes tabs or semicolons are used. Always confirm the delimiter before importing.
  • Encoding check
    If the CSV file uses a different character encoding (e.g., UTF-8, Shift-JIS), character corruption may occur during import. Check the file encoding beforehand and convert it to the correct one if necessary.

3. Importing CSV with the LOAD DATA INFILE Command

The most common way to import CSV files into MySQL is by using the LOAD DATA INFILE command, which makes it easy to bulk load large amounts of data into a table.

3.1 Basic Syntax

Here’s the basic syntax for importing a CSV file into MySQL:

LOAD DATA INFILE '/path/to/file.csv' INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
  • FIELDS TERMINATED BY: Defines the delimiter used between fields. A comma is the most common.
  • LINES TERMINATED BY: Defines the line delimiter, usually \n (newline).

3.2 Example Execution

In the following example, a file named user_data.csv is imported into the users table:

LOAD DATA INFILE '/path/to/user_data.csv' INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
  • IGNORE 1 LINES: Skips the first row (header row). Useful when the CSV contains column names in the first line.

3.3 File Path and Permission Notes

The file must be located where the MySQL server can access it. If the server cannot read the file, use LOAD DATA LOCAL INFILE to import the data from the client side.

4. Troubleshooting Common Issues

Below are solutions to typical problems encountered when importing CSV files into MySQL.

4.1 File Path Issues

If the file path is incorrect, you may see the error The MySQL server is not permitted to read from the file. In such cases, verify the correct path on the server, or use the LOAD DATA LOCAL INFILE option to load files from the client side.

LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

4.2 Character Encoding Issues

If the file is saved with a different encoding, garbled text may appear during import. To prevent this, confirm the file encoding and set the appropriate character set in MySQL:

SET NAMES 'utf8mb4';

 

5. Practical Example: Importing a CSV File

Let’s walk through the step-by-step process of importing a CSV file into MySQL.

5.1 Preparing the CSV File

Create a CSV file (data.csv) with the following content:

id,name,age
1,Taro Yamada,28
2,Hanako Sato,34
3,Ichiro Tanaka,45

5.2 Running the Command

To import the file into the users table, run this command:

LOAD DATA INFILE '/path/to/data.csv' INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

This command imports all rows starting from the second line into the users table.

6. Advanced: Error Handling and Duplicate Data

Here’s how to handle errors and duplicate data when importing CSV files into MySQL.

6.1 Handling Duplicate Data

If records with the same key already exist, use the REPLACE option to overwrite the existing rows with new data:

LOAD DATA INFILE '/path/to/data.csv' INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
REPLACE;

6.2 Error Handling

If you want to continue the process even when errors occur, use the IGNORE option. This will skip problematic rows while still importing the rest of the data:

LOAD DATA INFILE '/path/to/data.csv' INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
IGNORE;

 

7. Conclusion

Importing CSV files into MySQL is a powerful tool for managing large datasets and migrating data between systems efficiently. By mastering the basics and learning how to handle errors and duplicates, you can ensure reliable and stable data imports.

In future articles, we’ll also cover how to export data from MySQL databases to further enhance your data management skills.