- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
Updated on 04-Feb-2020 05:58:37
- Related Questions & Answers
- How can we import data from .CSV file into MySQL table?
- How can we import only specific columns from the text file, into MySQL table?
- How can we import the text file, having some line prefixes, into MySQL table?
- How can we MySQL LOAD DATA INFILE statement with ‘ENCLOSED BY’ option to import data from text file into MySQL table?
- How can we export all the data from MySQL table into a text file?
- How can we export all the data from MySQL table into a CSV file?
- How can we MySQL LOAD DATA INFILE statement with ‘FIELDS TERMINATED BY’ option to import data from text file into MySQL table?
- How can we import the text file, having data on the same line with a separator, into MySQL table?
- How can we insert data into a MySQL table?
- How can we export some field(s) from MySQL table into a text file?
- How can we export some field(s) from MySQL table into a CSV file?
- Plot data from a .txt file using matplotlib
- How can we import CSV files into MySQL tables by using mysqlimport?
- How can we import data from a text file having names of the columns in first row?
- How can we insert data into an existing MySQL table by using PHP script?
Advertisements