How To Import the CSV File In MySQL Using the Command Line

In this tutorial, we will learn how to import data from a CSV file into a MySQL database using the command line. Importing data from CSV files is a common task when working with MySQL databases, as it provides a quick and efficient way to insert data into a table.

This tutorial assumes you have already installed and set up the MySQL server on your computer and that you have access to the command line interface.

Step 1: Prepare the CSV file

Before we begin with the import process, it’s important to make sure the CSV file you wish to import is formatted correctly. The first row should contain the column names that correspond to the columns in the MySQL table you wish to import the data into.

Example CSV file content:

name,age,email
John,25,[email protected]
Jane,30,[email protected]
Bob,22,[email protected]

This example CSV file has three columns: name, age, and email. It also contains three rows of data.

Step 2: Create a MySQL table

In this step, we will create the MySQL table where the data from the CSV file will be inserted. You can either create a new table or use an existing one. For this tutorial, we will create a new table called employees with columns that match the CSV file.

To create the table, open the command line and log in to the MySQL server using the following command:

mysql -u USERNAME -p

Replace USERNAME with your MySQL username and enter your password when prompted.

Once you are logged in, create the new table by running the following command:

1
2
3
4
5
6
7
8
CREATE DATABASE IF NOT EXISTS mydata;
USE mydata;
 
CREATE TABLE employees (
  name VARCHAR(100),
  age INT,
  email VARCHAR(255)
);

This will create a new database called mydata (if it doesn’t already exist), a new table called employees, and three columns matching the structure of our example CSV file.

Step 3: Import the CSV file

To import the CSV file into the MySQL table, we will use the LOAD DATA INFILE command. Make sure that your command line’s current working directory is the location of the CSV file, and then run the following command:

1
2
3
4
5
6
LOAD DATA INFILE 'example.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Replace 'example.csv' with the path to your CSV file.

This command will load the data from the example.csv file and insert it into the employees table. The FIELDS TERMINATED BY ',' and ENCLOSED BY '"' options specify the delimiter and enclosing character for fields in the CSV file, respectively. The LINES TERMINATED BY '\n' option specifies the line separator character. The IGNORE 1 ROWS option tells MySQL to ignore the first row of the CSV file, which contains the column names.

Step 4: Verify the import

To verify the import, you can run a simple SELECT statement to retrieve the data from the employees table:

1
SELECT * FROM employees;

This will display the imported data. The output should look similar to the following:

+------+-----+-----------------------+
| name | age | email                 |
+------+-----+-----------------------+
| John |  25 | [email protected]  |
| Jane |  30 | [email protected]  |
| Bob  |  22 | [email protected] |
+------+-----+-----------------------+
3 rows in set (0.00 sec)

Full Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE DATABASE IF NOT EXISTS mydata;
USE mydata;
 
CREATE TABLE employees (
  name VARCHAR(100),
  age INT,
  email VARCHAR(255)
);
 
LOAD DATA INFILE 'example.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
 
SELECT * FROM employees;

Conclusion

In this tutorial, we have learned how to import data from a CSV file into a MySQL table using the command line. This is a convenient method to insert data into a MySQL database, especially when dealing with large datasets or when automating processes. With this knowledge, you can now efficiently import data from CSV files into your MySQL database.