How MySQL evaluates if we use any other escape character rather that back-slash (\) in a text file while importing the data from text file to MySQL table?

Back-slash(\) is the by default escape character for the MySQL and when we use it in the text file then we do not need to mention it in the query while importing the data from text file to table. But if we use any other character as escape character then it must be mentioned by using ESCAPED BY option in the query while importing the text file into a table. It can be understood with the help of the following example −

Suppose we are using star symbol (‘* ‘) as the escape character in a text file as follows −

id,Name,Country,Salary
105,Chum*,Marsh,USA,11000
106,Danny*,Harrison,AUS,12000

Now while importing this text file into MySQL table then we need to mention ‘ESCAPED BY’ option in the query as follows −

mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee6_tbl FIELDS TERMINATED BY ',' ESCAPED BY ‘*’ IGNORE 1 ROWS;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

Now, we can see what has been imported with the help of the following query −

mysql> Select * from employee6_tbl;
+------+----------------+----------+--------+
| Id   | Name           | Country  | Salary |
+------+----------------+----------+--------+
| 105  | Chum,Marsh     | USA      | 11000  |
| 106  | Danny,Harrison | AUS      | 12000  |
+------+----------------+----------+--------+
2 rows in set (0.00 sec)

Srinivas Gorla

Published on 20-Feb-2018 07:44:46

• Related Questions & Answers
• What is the use of escape character (\) in text file while importing the data from text file to MySQL table?
• How MySQL evaluates the blank line between two lines written in the text file while importing that text file into MySQL table?
• How can we upload the changed value, rather than written in a text file, of column(s) while importing that text file into MySQL table?
• How can we export all the data from MySQL table into a text file?
• What kind of settings can we do to a text file by query while exporting the values from MySQL table into a text file?
• How MySQL evaluates if we export the data to CSV file from a table which contains a NULL value(s)?
• Table type while importing data from flat file in SAP HANA
• How can we import only specific columns from the text file, into MySQL table?
• How can we export some field(s) from MySQL table into a text file?
• While adding the numbers contained in quotes, how MySQL evaluates if we write non-numeric text after a number?
• While adding the numbers contained in quotes, how MySQL evaluates if we write non-numeric text before a number?
• How can we import the text file, having some line prefixes, into MySQL table?
• How can we store any other value than \N in CSV file if we export the data to CSV file from a table which contains a NULL value(s)?
• How can we MySQL LOAD DATA INFILE statement with ‘ENCLOSED BY’ option to import data from text file into MySQL table?
• How can we import data from .txt file into MySQL table?