What is the use of escape character (\) in text file while importing the data from text file to MySQL table?

MySQLMySQLi Database

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)
raja
Published on 20-Feb-2018 07:42:55
Advertisements