How to import a CSV file into a table in MySQL 8

Introduction

Working with databases often involves importing data from various file formats. Comma Separated Values (CSV) is a widely-used data format due to its simplicity and compatibility with numerous applications. In this tutorial, we’ll discuss how you can import a CSV file into a MySQL 8 database table step by step. From basic import to handling complex data types and securing your database, we’ll cover it all with practical examples.

Prerequisites

• MySQL 8 installed and running
• Access to the MySQL command-line tool or a database management interface like phpMyAdmin
• A CSV file ready to be imported
• Knowledge of MySQL commands and CSV file structure

Creating the Table

Before importing the CSV data, create a table in MySQL with a structure that matches the data. Here’s an example of a simple table creation:

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

Basic Import with LOAD DATA

The LOAD DATA INFILE command is one of the most efficient ways to import CSV data into MySQL. The following example demonstrates a basic import operation:

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

Output:

Query OK, x rows affected (x.xx sec)
Records: x Deleted: x Skipped: x Warnings: x

This code assumes your CSV file contains headers which are to be ignored (hence the ‘IGNORE 1 ROWS’). Adjust the path, delimiters, and line terminators based on your specific file’s format.

Setting the Character Set

If your CSV file includes special characters, you need to specify the character set:

LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE users
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Handling Duplicate Records

When importing data, you might encounter duplicate records. MySQL allows you to handle duplicates with ease. The following code updates the existing records if a duplicate key is found:

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

ON DUPLICATE KEY UPDATE
name=VALUES(name),
email=VALUES(email),
signup_date=VALUES(signup_date);

Secure File Privileges

The LOAD DATA INFILE command requires a secure-file-priv option. This option limits the locations from which you can load files. Ensure MySQL has the privilege to access the file path. You can check the allowed paths using this command:

SHOW VARIABLES LIKE 'secure_file_priv';

Importing CSV Data via phpMyAdmin

If you have access to phpMyAdmin, it offers a user-friendly interface to import CSV files:

1. Select the target database and table.
2. Click on the ‘Import’ tab.
3. Choose your CSV file.
4. Select format as ‘CSV’.
5. Fill in the options similar to the options in the LOAD DATA INFILE command.
6. Click ‘Go’ to start the import.

phpMyAdmin will execute a LOAD DATA INFILE operation in the back-end.

Dealing with Large CSV Files

Large CSV files may cause timeouts or memory issues. To address this, you can increase PHP’s timeout and memory limits. Alternatively, split your CSV into smaller chunks. Command-line imports generally handle larger files better than web interfaces like phpMyAdmin.

Conclusion

In conclusion, importing a CSV file into MySQL 8 involves familiarity with the LOAD DATA INFILE command and the ability to tailor it to the specific needs of your dataset. Throughout this tutorial, we’ve seen numerous examples that will assist you in handling CSV imports, from simple to complex scenarios. The process is quite straightforward once you understand the commands and how to manage different types of data and potential duplicates.