Practical MySQL & MariaDB

How to Import a CSV File into MySQL: Complete Guide with LOAD DATA INFILE and Troubleshooting

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 need to bulk import information collected from multiple data sources into a database, or automatically process data from external systems, CSV import becomes extremely useful.

Common Use Cases for CSV Import

  • Processing Large Volumes of Data: Instead of manually entering thousands of records, you can process them quickly using CSV import.
  • Data Migration: Convenient when importing data exported from other systems into MySQL.
  • Regular Data Updates: Systems that require periodic data updates can automate the process using CSV files.

2. Prerequisites

Before importing a CSV file into MySQL, several settings and preparations are required. These ensure a smooth and error-free data import process.

2.1 Required Environment

  • MySQL Server Installation
    It is assumed that MySQL is properly installed. Make sure MySQL is running correctly in your environment, whether it is Windows, macOS, or Linux.
  • Verify Required Permissions
    To import a CSV file, the MySQL user must have appropriate privileges. In particular, execution permission for LOAD DATA INFILE is required. Without this privilege, the import may be denied.

2.2 CSV File Format

  • Delimiter
    CSV files are typically comma-separated, but in some cases, tabs or semicolons may be used. Check which delimiter is being used beforehand.
  • Check Encoding
    If a CSV file is saved with a different character encoding (such as UTF-8 or Shift-JIS), character corruption may occur during import. Verify the file encoding in advance and convert it if necessary.

3. Importing CSV Using the LOAD DATA INFILE Command

The most common way to import a CSV file into MySQL is by using the LOAD DATA INFILE command. This command allows you to easily load large amounts of data into a database.

3.1 Basic Syntax

The following is the basic syntax used to import 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: Specifies the field delimiter. A comma is commonly used.
  • LINES TERMINATED BY: Specifies the line delimiter. Usually \n (newline) is used.

3.2 Example Execution

The following example imports a file named user_data.csv 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) of the CSV file. This option is useful when column names are included in the first line.

3.3 Important Notes: File Path and Permissions

When specifying the file path, the file must be located in a directory accessible by the MySQL server. If the server cannot access the file, use LOAD DATA LOCAL INFILE to load data from the client side.

4. Common Problem Solutions

Below are solutions to common problems that occur during CSV file import.

4.1 File Path Issues

If the file path is not specified correctly, you may see an error message such as The MySQL server is not permitted to read from the file. In this case, verify that the server-side file path is correct. You can also use the LOAD DATA LOCAL INFILE option to load a local file.

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 character encoding, character corruption may occur during import. To avoid this issue, check the file encoding beforehand and specify the appropriate character set in MySQL.

SET NAMES 'utf8mb4';

5. Practical Example: CSV Import Procedure

Here, we explain the step-by-step procedure for importing an actual 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 Executing the Command

Run the following command to import the created CSV file into the users table.

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

This command imports data starting from the second row of the CSV file into the users table.

6. Advanced: Error Handling and Duplicate Data Prevention

This section explains how to handle errors during CSV import and how to deal with duplicate data.

6.1 Handling Duplicate Data

If data with the same key already exists, you can use the REPLACE option to overwrite duplicate 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 errors occur during import but you want to continue processing, use the IGNORE option. This skips problematic rows and imports the remaining valid 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 efficiently handling large datasets and migrating data between systems. By understanding the basic procedures and implementing proper error handling and duplicate prevention strategies, you can achieve stable and reliable data imports.

In future articles, we will also explain how to export data from a MySQL database in detail. Continue improving your data management skills.