How can we MySQL LOAD DATA INFILE statement with ‘FIELDS TERMINATED BY’ option to import data from text file into MySQL table?

‘FIELDS TERMINATED BY’ option should be used when the text file which we want to import into MySQL table is having the values which are separated by a comma(,) or maybe with any other separator like a colon(:), semicolon(;) etc. It can be understood with the help of the following example −

Example

Suppose we are having the following data, separated by a semicolon(;), in the text file ‘A.txt’ which we want to import into a MySQL file −

100;Ram;IND;15000
120;Mohan;IND;18000

Now with the help of the following query by using the option ‘FIELDS SEPARATED BY ‘we can import the data into MySQL table −

mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee12_tbl FIELDS TERMINATED BY ';';
Query OK, 2 rows affected (0.04 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> Select * from employee12_tbl;
+------+----------------+----------+--------+
| Id   | Name           | Country  | Salary |
+------+----------------+----------+--------+
| 100  | Ram            | IND      |  15000 |
| 120  | Mohan          | IND      |  18000 |
+------+----------------+----------+--------+
2 rows in set (0.00 sec)

Abhinaya

Published on 20-Feb-2018 12:42:13