MySQL – Load data from csv

By | October 29, 2014

The LOAD DATA INFILE statement will reads rows from a text/csv file and insert the data into a table at high speed. We need to pass the complete path/location of the file in the query.

LOAD DATA INFILE is similar to that of SELECT … INTO OUTFILE. To write data from a table to a file we use the SELECT … INTO OUTFILE statement and whereas to read data from a file to a table we use LOAD DATA INFILE. The syntax of the FIELDS and LINES clauses is same for both the statements. Both clauses are optional, but FIELDS must precede LINES if both are specified.

The syntax for the LOAD DATA INFILE statement is shown below.

LOAD DATA LOCAL INFILE '<Complete File Path>'
INTO TABLE <Table Name>
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n'
(<All list of Fields>);

Example for the LOAD DATA INFILE :

1. Create a sample csv file with name sample.csv as shown below.

id,name,dept
123,test1,dept1
1234,test2,dept2

2. Create a table with name employee.

CREATE TABLE `employee` (
 `id` int(5) NOT NULL,
 `name` varchar(50) DEFAULT NULL,
 `department` varchar(50) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

3. Run the below LOAD DATA LOCAL INFILE statement which will read the data from the CSV and insert them in to the employee table.

LOAD DATA LOCAL INFILE 'D:\\sample.csv' 
INTO TABLE `myexamples`.`employee` 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES
(`id`, `name`, `department`);

We need to pass the complete path of the csv file. The IGNORE 1 LINES will ignore the header from the CSV file. The above query will insert two rows in the employee table excluding the 1st row.

Leave a Reply

Your email address will not be published. Required fields are marked *