Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What is the use of escape character () in text file while importing the data from text file to MySQL table?
Use of escape character (\) would become very essential when we want to insert a comma or any other character between the values of a filed. It can be understood with the help of an example. Suppose we want to import the data from a text file named A.txt, having the following data, into a MySQL table −
id, Name, Country, Salary 105, Chum, Marsh,USA, 11000 106, Danny, Harrison,AUS, 12000
Here, we can see that the filed name has two values first name, last name separated by a comma. Now, the following query will import the data from the above text file ‘A.txt’ into ‘employee4_tbl’ −
mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee4_tbl FIELDS TERMINATED BY ',' IGNORE 1 ROWS; Query OK, 2 rows affected, 4 warnings (0.05 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 4
We can see there are 4 warnings when we import the data from ‘A.txt’ to the table. The following query will show us what has been inserted in the table −
mysql> Select * from employee4_tbl; +------+---------+----------+--------+ | Id | Name | Country | Salary | +------+---------+----------+--------+ | 105 | Chum | Marsh | 0 | | 106 | Danny | Harrison | 0 | +------+---------+----------+--------+ 2 rows in set (0.00 sec)
The above result set shows that MySQL predicts the ‘lastname’, which was the value of field ‘name’, as the value of filed ‘Country’.
The above problem can be eliminated by putting the escape character in a text file as follows −
id, Name, Country, Salary 105, Chum\, Marsh,USA, 11000 106, Danny\, Harrison,AUS, 12000
Now import this text file into MySQL table with the help of the following query −
mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee5_tbl FIELDS TERMINATED BY ',' IGNORE 1 ROWS; Query OK, 2 rows affected (0.03 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
We can see there are no warnings as there were earlier in the above query. Now, we can see what has been imported with the help of the following query −
mysql> Select * from employee5_tbl; +------+----------------+----------+--------+ | Id | Name | Country | Salary | +------+----------------+----------+--------+ | 105 | Chum,Marsh | USA | 11000 | | 106 | Danny,Harrison | AUS | 12000 | +------+----------------+----------+--------+ 2 rows in set (0.00 sec)
279 Views
