- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
How can we MySQL LOAD DATA INFILE statement with â€˜ENCLOSED BYâ€™ option to import data from text file into MySQL table?
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 −
Followings are the comma-separated values in A.txt file −
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.
- Related Articles
- 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 data from .txt file into MySQL table?
- How can we import data from .CSV 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 export all the data from MySQL table into a text file?
- 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 export all the data from MySQL table into a CSV file?
- How can we insert data into a MySQL table?
- How can we export some field(s) from MySQL table into a text file?
- How should I enable LOAD DATA LOCAL INFILE in my.cnf in MySQL?
- What is MySQL LOAD DATA statement?
- MySQL statement to copy data from one table and insert into another table
- How can we insert data into an existing MySQL table by using PHP script?
- How can we copy data with some condition/s from existing MySQL table?