MySQL LOAD DATA INFILE using LOAD DATA INFILE to load large amount of data to database
Example
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.
