How to Load Data INFILE in MySQL

In this tutorial, we aim at exploring how to import data from CSV using data INFILE in MySQL database.

To read data from a text file or a CSV at a very high speed, we use the LOAD DATA INFILE statement in MySQL. Moreover, a LOCAL word is introduced if the file needs to be considered by the host.

Before reading information from a file, we must ensure that the CSV or text file resides in the database and should have the permissions to be read.

The basic syntax of the LOAD DATA INFILE statement is as follows.

LOAD DATA LOCAL INFILE "./csv_file.csv" INTO TABLE database_name.name_of_table; 

We now read from a CSV file named fileCSV.csv into the students database. We shall name our table in the students database as student_details.

However, we create a dummy dataset to work on before we begin. We create a table, student_details, along with a few rows.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

Data in our CSV file contains information like stu_id, stu_firstName, and stu_lastName but with different stu_id. A snapshot of our CSV file is as follows.

Let us first begin by creating a database named students. We can do this with the following query.

CREATE DATABASE students;

We can write the following query to import our CSV file into our students database.

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/fileCSV.csv' into table student_details fields terminated by ',' ENCLOSED BY '"' IGNORE 1 ROWS;

This would give the following output:

Query OK, 6 rows affected (0.01 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

If we check the student_details table in the students database, we will get the following output:

1	Preet	Sanghavi
2	Rich	John
3	Veron	Brow
4	Geo	Jos
5	Hash	Shah
6	Sachin	Parker
7	David	Miller
9	Rich	John
10	Veron	Brow
11	Geo	    Jos
12	Hash	Shah
13	Sachin	Parker
14	David	 Miller

We can infer from the above code block that we have updated our student_details table. We now have the data in the CSV file appended into our table.

Therefore, with the help of the LOAD DATA INFILE method, we can efficiently write data from an external CSV or text file to a table in MySQL.