How can we import data from .txt file into MySQL table?
It can be done with the help of LOAD DATA INFILE statement. To illustrate the concept we are having the following data, separated by tab, in ‘A.txt’ whose path is d:/A.txt −
100 John USA 10000
101 Paul UK 12000
102 Henry NZ 11000
103 Rick USA 17000
104 Corey USA 15000
We want to load the data of A.txt into the following table named employee_tbl −
mysql> Create table employee_tbl(Id Int, Name varchar(20), Country Varchar(20),Salary Int);
Query OK, 0 rows affected (0.91 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 employee_tbl;
Query OK, 5 rows affected (0.15 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
mysql> Select * from employee_tbl;
+------+-------+---------+--------+
| Id | Name | Country | Salary |
+------+-------+---------+--------+
| 100 | John | USA | 10000 |
| 101 | Paul | UK | 12000 |
| 102 | Henry | NZ | 11000 |
| 103 | Rick | USA | 17000 |
| 104 | Corey | USA | 15000 |
+------+-------+---------+--------+
5 rows in set (0.00 sec)
The above result set shows that the data from A.txt file has been transferred to the table.
Published on 20-Feb-2018 11:57:19
