Import CSV File in Database - (MySQL Misc)

When working with MySQL, it's common to need to import data from CSV files into a database table. In this tutorial, we'll go over how to import a CSV file into MySQL.

Syntax

To import a CSV file into MySQL, we'll use the LOAD DATA LOCAL INFILE statement:

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

This statement specifies the path to the CSV file, the table name to import the data into, and the delimiter and text qualifier used in the CSV file.

Example

Let's say we have a CSV file "employees.csv" with the following data:

"id","name","department","salary"
"1","John Doe","Marketing","50000"
"2","Jane Smith","Sales","60000"
"3","Bob Johnson","IT","70000"

We can import this data into a MySQL table called "employees" using the following command:

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

This will import the data from the CSV file into the "employees" table in the MySQL database.

Output

When the command is successful, MySQL will return a message similar to the following:

Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 3

This message indicates that the data from the CSV file was imported into the "employees" table, and there were no errors during the import.

Explanation

In the example above, we used the LOAD DATA LOCAL INFILE statement to import data from a CSV file into a MySQL table. We specified the path to the CSV file, the table name to import the data into, and the delimiter and text qualifier used in the CSV file.

We also used the IGNORE 1 ROWS clause to skip the first row of the CSV file, which contained column headers.

Use

Importing data from CSV files is a common task when working with MySQL databases. This process can be used to quickly import large amounts of data into a database table.

Important Points

• The LOAD DATA LOCAL INFILE statement is used to import data from CSV files into MySQL.
• The file path and delimiter and text qualifier used in the CSV file must be specified in the LOAD DATA LOCAL INFILE statement.
• The IGNORE X ROWS clause can be used to skip the first X rows of the CSV file.
• Use caution when importing data from CSV files, as any errors or inconsistencies in the data can lead to issues with the database.

Summary

In this tutorial, we went over how to import data from a CSV file into a MySQL table using the LOAD DATA LOCAL INFILE statement. We covered the syntax, example, output, explanation, use, and important points of this process. With this knowledge, you can now import data from CSV files into MySQL databases with confidence.