Efficient CSV Import into MySQL + Common Error Fixes

1. Introduction

2. Basic Steps for Importing Data into MySQL

Preparing the Database and Tables

CREATE DATABASE example_db;
USE example_db;
CREATE TABLE example_table (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

Verifying the Format of the Data to Import

1,John Doe,30
2,Jane Smith,25

• Delimiter: It should be separated by commas (,).
• Encoding: It is recommended to be saved in UTF-8.

LOAD DATA INFILE Command Usage

LOAD DATA INFILE '/path/to/example.csv'
INTO TABLE example_table
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '
';

• FIELDS TERMINATED BY: Specifies the field delimiter.
• ENCLOSED BY: Specifies the character that encloses values (e.g., double quotes).
• LINES TERMINATED BY: Specifies the line terminator.

3. Import Method Using GUI

How to Use MySQL Workbench

1. Launch the Data Import Wizard Open MySQL Workbench and select Server → Data Import from the menu bar.
2. Select File Specify the file to import using “Import from Self-Contained File”.
3. Select Target Database Choose the destination database under “Default Target Schema”.
4. Execute Click “Start Import” to begin the import.

How to Use phpMyAdmin

1. Log in to phpMyAdmin Access phpMyAdmin in a browser and log in.
2. Select Database Choose the destination database from the left-hand menu.
3. Open the Import Tab Click the “Import” tab at the top.
4. Select File Use the “Choose File” button to specify the CSV file.
5. Specify Format Select the file format (e.g., CSV) and encoding.
6. Execute Press the “Go” button to complete the import.

4. How to Import from the Command Line

mysql Command Import

mysql -u root -p example_db < /path/to/example.sql

• -u: Specifies the username.
• -p: Option that prompts for a password.
• example_db: Name of the target database for import.

Using the source Command

mysql -u root -p
USE example_db;
SOURCE /path/to/example.sql;

OS-Specific Considerations

• Windows: Use backslashes for file paths ().
• Linux/Mac: Use forward slashes for file paths (/).

5. Points to Note When Importing CSV Files

Encoding Issues

• Recommended encoding: UTF-8 is generally recommended for MySQL.
• Using Shift-JIS: CSV files created in Windows are often in Shift-JIS, so converting them to UTF-8 can prevent problems.

• Windows: In Notepad, use “Save As” to view the character encoding.
• Linux/Mac: You can check a file’s encoding with the file command.

file -i example.csv

Configuration of local_infile

1. Check the current status

   SHOW VARIABLES LIKE 'local_infile';

2. Enable when connecting the client

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

3. Enable at the server level

• Edit MySQL’s configuration file (my.cnf or my.ini) and add the following line. [mysqld]

CSV Data Consistency

1. Matching column count If the number of columns in the CSV file does not match the table’s column count, an error occurs. Edit the CSV file as needed.
2. Handling NULL values Blank cells are treated as NULL in MySQL. To convert NULL to a specific value, specify it in LOAD DATA INFILE as shown below.

   LOAD DATA INFILE '/path/to/example.csv'
   INTO TABLE example_table
   FIELDS TERMINATED BY ',' 
   LINES TERMINATED BY '
'
   (id, name, age)
   SET age = IF(age='', NULL, age);

3. Date format MySQL date types (DATE, DATETIME) use the YYYY-MM-DD format by default. If you’re using a different format (e.g., MM/DD/YYYY), you need to convert the data before importing.

Delimiter and Enclosure Settings

• When the delimiter is a tab If the CSV file is tab‑delimited, specify it as follows.

  FIELDS TERMINATED BY '    '

• When values are enclosed in double quotes Specify the enclosure character.

  FIELDS ENCLOSED BY '"'

6. Practical Examples of Export and Import

Steps for Data Export

Exporting an Entire Database

mysqldump -u root -p example_db > example_db.sql

• -u root: Specifies the username.
• -p: Option that prompts for a password.
• example_db.sql: The filename for the export destination.

Exporting Specific Tables

mysqldump -u root -p example_db example_table > example_table.sql

Exporting Multiple Databases

mysqldump -u root -p --databases db1 db2 > multiple_dbs.sql

Exporting All Databases

mysqldump -u root -p --all-databases > all_databases.sql

Steps for Data Import

Importing an Entire Database

mysql -u root -p example_db < example_db.sql

Importing Specific Tables

mysql -u root -p example_db < example_table.sql

Combining Database Creation and Import

1. Create the database:

   CREATE DATABASE example_db;

2. Import into the created database:

   mysql -u root -p example_db < example_db.sql

Important Considerations for Export and Import

1. Verify Export File After exporting, check the file’s contents to ensure it doesn’t contain an incomplete backup.

   less example_db.sql

2. Matching Encoding Make sure the character set matches between export and import. UTF-8 is used by default, but you can specify the --default-character-set option if needed.

   mysqldump --default-character-set=utf8 -u root -p example_db > example_db.sql

3. Permission Settings When importing, ensure the user has the appropriate privileges.

   GRANT ALL PRIVILEGES ON example_db.* TO 'user'@'localhost';

7. Troubleshooting

Common Errors and Their Solutions

ERROR 1148 (42000): The used command is not allowed with this MySQL version

• Cause LOAD DATA INFILE command is disabled. For security reasons, many MySQL installations have the local_infile option disabled by default.
• Solution

1. Check the current setting of local_infile. SHOW VARIABLES LIKE 'local_infile';
2. Enable it when connecting the client. mysql --local-infile=1 -u root -p
3. Modify the server configuration to enable it permanently.
   • Add the following to the MySQL configuration file (my.cnf or my.ini): [mysqld]

ERROR 1366: Incorrect string value

• Cause occurs when the data being imported does not match the database’s character set configuration. This is especially common with multibyte characters such as Japanese.
• Solution

1. Check the character set of the database and tables. SHOW VARIABLES LIKE 'character_set%';
2. Unify the character set as needed. Below is an example of setting it to UTF-8. ALTER TABLE example_table CONVERT TO CHARACTER SET utf8mb4;
3. Specify the character set during export and import. mysqldump --default-character-set=utf8mb4 -u root -p example_db > example_db.sql mysql --default-character-set=utf8mb4 -u root -p example_db < example_db.sql

ERROR 1062: Duplicate entry

• Cause the data being imported contains duplicate rows that violate the table’s primary key or unique constraints.
• Solution

1. Ignore duplicate rows: LOAD DATA INFILE '/path/to/example.csv' INTO TABLE example_table FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ' IGNORE;
2. Update on duplicate: INSERT INTO example_table (id, name, age) VALUES (1, 'John Doe', 30) ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age);

ERROR 1045: Access denied for user

• Cause insufficient privileges to access the target database.
• Solution

1. Check the user’s privileges. SHOW GRANTS FOR 'user'@'localhost';
2. Grant the necessary privileges. GRANT ALL PRIVILEGES ON example_db.* TO 'user'@'localhost'; FLUSH PRIVILEGES;

Issues and Countermeasures for Large-Scale Data Imports

Out-of-Memory Errors

• Cause importing a large amount of data at once can exhaust memory.
• Solution

1. Split the data and import it in parts. On Linux/Mac you can use the splitOLDER_36___split -l 1000 large_file.csv part_
2. Import each part file sequentially.

Server Timeout

• Cause importing large data does not finish within the allowed time, causing a timeout.
• Solution increase the timeout settings in MySQL.

  SET GLOBAL net_read_timeout = 600;
  SET GLOBAL net_write_timeout = 600;

8. FAQ (Frequently Asked Questions)

Q1: Is it possible to import only specific columns from a CSV file?

LOAD DATA INFILE '/path/to/example.csv'
INTO TABLE example_table
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '
'
(id, @dummy, age);

Q2: What are efficient methods for importing large volumes of data?

1. Temporarily disable indexes By disabling indexes before the import and recreating them after the import completes, performance improves.

   ALTER TABLE example_table DISABLE KEYS;
   -- import process
   ALTER TABLE example_table ENABLE KEYS;

2. Leverage bulk insert The LOAD DATA INFILE command is ideal for processing large data sets. To further improve import speed, you can use the LOCAL option to load files from the client side.

   LOAD DATA LOCAL INFILE '/path/to/large_file.csv' INTO TABLE example_table;

3. File splitting On Linux or macOS, you can use the split command to divide a large file and then import it in smaller chunks sequentially, which is also effective.

Q3: Are there ways to validate data during import?

1. Use a temporary table Instead of importing data directly into the production table, import it into a temporary table first and then verify the data.

   CREATE TEMPORARY TABLE temp_table LIKE example_table;
   LOAD DATA INFILE '/path/to/example.csv' INTO TABLE temp_table;
   -- after verifying data, move to production table
   INSERT INTO example_table SELECT * FROM temp_table;

2. Check error output If you add the ERRORS option to the LOAD DATA INFILE command, you can obtain detailed error information.

   LOAD DATA INFILE '/path/to/example.csv'
   INTO TABLE example_table
   FIELDS TERMINATED BY ',' 
   LINES TERMINATED BY '
'
   IGNORE 1 LINES
   (@col1, @col2, @col3)
   SET col1 = IF(@col1='', NULL, @col1),
       col2 = @col2,
       col3 = @col3;

Q4: MySQL timed out during data import. What should I do?

1. Extend timeout duration Adjust MySQL’s timeout settings.

   SET GLOBAL net_read_timeout = 600;
   SET GLOBAL net_write_timeout = 600;

2. Split the file and import By splitting a large file before importing, you can prevent timeouts.
3. Check MySQL logs Check the error log to identify the cause of the timeout.

Q5: How can I treat blank cells as NULL during import?

LOAD DATA INFILE '/path/to/example.csv'
INTO TABLE example_table
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '
'
(name, age)
SET age = IF(age='', NULL, age);

9. Summary

Key Points

1. Understanding the Basic Procedure

• We explained how to use the LOAD DATA INFILE command and the mysql command to import data efficiently.
• We also introduced easy methods using GUI tools (MySQL Workbench, phpMyAdmin).

1. CSV File Considerations

• We covered important points for maintaining data integrity, such as encoding (UTF-8 recommended), matching column counts, and handling NULL values.

1. Practical Examples of Export and Import

• We provided step-by-step instructions for exporting and importing using the mysqldump command.
• These procedures can also be applied to data migration between servers and backup tasks.

1. Troubleshooting

• We detailed common errors (e.g., ERROR 1148 and ERROR 1366) and how to resolve them.
• We introduced best practices for handling large data sets and timeouts.

1. FAQ (Frequently Asked Questions)

• We covered topics such as handling specific columns during import, efficiently importing large data sets, and data validation methods to answer readers’ questions.

Further Reference Resources

• MySQL Official Documentation (English) MySQL Documentation

Next Steps

• Apply the Knowledge You’ve Gained Please strengthen your skills by trying the steps from this article in real projects.
• Study Related Topics By also learning about backup strategies and security settings in database management, you will further improve your practical abilities.