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

MySQLMySQLi Database

Sometimes the input text files have the text fields enclosed by double quotes and to import data from such kind of files we need to use the ‘ENCLOSED BY’ option with LOAD DATA INFILE statement. We are considering the following example to make it understand −

Example

Followings are the comma-separated values in A.txt file −

100,”Ram”,”INDIA”,25000
101,”Mohan”,”INDIA”,28000

We want to import this data into the following file named employee2_tbl −

mysql> Create table employee2_tbl(Id Int, Name Varchar(20), Country Varchar(20),Salary Int);

Query OK, 0 rows affected (0.1 sec)

Now, the transfer of data from a file to a database table can be done with the help of the following table −

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

mysql> Select * from employee2_tbl;
+------+-------+---------+--------+
| Id   | Name  | Country | Salary |
+------+-------+---------+--------+
| 100  | Ram   | INDIA   |  25000 |
| 101  | Mohan | INDIA   |  28000 |
+------+-------+---------+--------+
2 rows in set (0.00 sec)

The above result set shows that the data from A.txt file has been transferred to the table.

raja
Published on 20-Feb-2018 07:36:02
Advertisements