MySQL loading a tab-delimited file into a table with special characters
mysql, sql
Solution
You have to specify `ESCAPED BY` option when loading your file. In the following example I'm assuming your fields are divided by tab (which is default behavior) and file is located on clients host:
LOAD DATA LOCAL INFILE '/path/to/file/my.txt' INTO TABLE t
FIELDS TERMINATED BY '\t' ESCAPED BY '\b';
Problem
I want to be able to load a file into a MySQL table that has backslashes in it like this: ``` Fred Los Angeles I am Fred Nick Madison Great Lakes Whoo JOHN San Diego Hello world!\ Bob NYC Big apple ``` User JOHN put a `\` at the end of column 3. When I load this into a table, MySQL is interpreting the backslash as some kind of line continuation and concatenating Row 3, Col 3) with (Row 4, Col1). I can't just copy and paste to fix it since there are millions of rows like this with backslashes in various places causing havoc. What is the proper way to load a tab-delimited file into a MySQL table with backslashes? Should I be enclosing each (column) field in quotes? Should I write a parser that removes special characters?