using LOAD DATA INFILE to load large amount of data to database

Consider the following example assuming that you have a ‘;’-delimited CSV to load into your database.

1;max;male;manager;12-7-1985
2;jack;male;executive;21-8-1990
.
.
.
1000000;marta;female;accountant;15-6-1992

Create the table for insertion.

CREATE TABLE `employee` ( `id` INT NOT NULL ,
                          `name` VARCHAR NOT NULL, 
                          `sex` VARCHAR NOT NULL ,
                          `designation` VARCHAR NOT NULL ,
                          `dob` VARCHAR NOT NULL   );

Use the following query to insert the values in that table.

LOAD DATA INFILE 'path of the file/file_name.txt' 
INTO TABLE employee
FIELDS TERMINATED BY ';' //specify the delimiter separating the values
LINES TERMINATED BY '\r\n'
(id,name,sex,designation,dob)

Consider the case where the date format is non standard.

1;max;male;manager;17-Jan-1985
2;jack;male;executive;01-Feb-1992
.
.
.
1000000;marta;female;accountant;25-Apr-1993

In this case you can change the format of the dob column before inserting like this.

LOAD DATA INFILE 'path of the file/file_name.txt' 
INTO TABLE employee
FIELDS TERMINATED BY ';' //specify the delimiter separating the values
LINES TERMINATED BY '\r\n'
(id,name,sex,designation,@dob)
SET date = STR_TO_DATE(@date, '%d-%b-%Y');

This example of LOAD DATA INFILE does not specify all the available features.

You can see more references on LOAD DATA INFILE here.

Found a mistake? Have a question or improvement idea? Let me know.

Table Of Contents