How can we import data from .txt file into MySQL table?

MySQL MySQLi Database

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.

Radhakrishna

Published on 20-Feb-2018 07:27:19

Previous Page Print Page