CODEWITHSUNDEEP
mysqlimportcharacter
user2571369
user2571369

Reputation: 115

How to import txt file into MySQL keeping the special characters

I want to import a txt file into mysql database using command

LOAD DATA LOCAL INFILE 'c:/mydatabase.txt' INTO TABLE mytable COLUMNS TERMINATED BY '\t'

However, one column of the data is a filepath which contains special character like "\". I found the import command above just skipped all the "\" in the pathname string.

Is there any way to keep the special charater when import data to mysql?

Upvotes: 2

Views: 3541

Answers (1)

Jeremy Smyth
Jeremy Smyth

Reputation: 23513

LOAD DATA INFILE also supports a FIELDS ESCAPED BY clause, which allows you to specify which character MySQL uses to identify special characters within a string, such as single-quotes within a single-quoted string, or tab characters (as you have in your FIELDS TERMINATED BY clause, represented as \t).

The default value for the FIELDS ESCAPED BY clause is \, a backslash, which is why your path characters are being interpreted by MySQL and then ignored; escaping a non-special character simply returns that character, so \1 simply returns 1. You would, however, have problems with filenames such as c:\tmp\newfile because both \t and \n are interpreted as special characters.

If you leave the escape character blank by doing FIELDS ESCAPED BY '', your import won't interpret backslashes, but you might have other problems if there are characters in the import that need escaping. You'll know this by finding out what escape character was used to generate the text file, then you can use that in your FIELDS ESCAPED BY clause.

Upvotes: 1

Related Questions