MySql: Insert values into Table from File Example

Inserting huge data into myql database is very simple if your data is in a file separated with delimiters. For example if you have a CSV ( Comma Separated Values) file, then you can use the below syntax to load data from the csv file into mysql table.

Consider you have a csv file named 'test.csv' with the following content,

01,SHARON,FEMALE,20

02,NAVEEN,MALE,24

03,ANITA,FEMALE,22

04,DAVID,MALE,23

The table named "employee" in which the above content is going to be inserted is described as,

+--------+-------------+------+-----+---------+-------+

| Field  | Type        | Null | Key | Default | Extra |

+--------+-------------+------+-----+---------+-------+

| id     | int(11)     | NO   |     | NULL    |       |

| name   | varchar(30) | YES  |     | NULL    |       |

| gender | varchar(10) | YES  |     | NULL    |       |

| age    | int(11)     | YES  |     | NULL    |       |

+--------+-------------+------+-----+---------+-------+

Use the below syntax to insert the test.csv file to the employee table,

load data local infile 'D:\\test.csv' into table employee

fields terminated by ','

enclosed by '"'

lines terminated by '\n'

(id,name,gender,age);

Now check your table,

mysql> select * from employee;

+----+--------+--------+------+

| id | name   | gender | age  |

+----+--------+--------+------+

|  1 | SHARON | FEMALE |   20 |

|  2 | NAVEEN | MALE   |   24 |

|  3 | ANITA  | FEMALE |   22 |

|  4 | DAVID  | MALE   |   23 |

+----+--------+--------+------+

4 rows in set (0.00 sec)

Execute the above code in your mysql server,followed by 'select * from employee' to ensure that data from the file is properly inserted into your table. In the above mysql query, 'fields terminated by' indicates the delimiter in your file, which in this case is a comma. The 'enclosed by' and 'lines terminated' by are optional with which you can specify  whether columns are enclosed with double quotes and the format of line breaks. The parameters in the circular brackets indicate the fields in your table where the data should be accommodated.

Please leave your valuable comments and queries on this post in the comments section in order for me to improve my writing skills and to showcase better posts in future. Thanks in advance.

Subscribe to GET LATEST ARTICLES!