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;