CODEWITHSUNDEEP
mysqlcsv
zishan paya
zishan paya

Reputation: 513

Not able load '\' character in mysql db

I am not able to load '\' character from CSV file into mysql db version 6.1

Suppose below is my excel table converted to CSV.

------------------------------
ColumnHeader1 | ColumnHeader2 |
------------------------------
BA\           |Pune           |
-------------------------------

Mysql command: 

LOAD DATA LOCAL INFILE 'D:\\mydatafile.csv' 
INTO TABLE mydb.mytable FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES
(ColumnHeader1,ColumnHeader2);

when I run the select query in above table, I got below output which is not similar to above table. This was working perfect in mysql 6.1. Now upgraded to 6.2 created problem.

------------------------------
ColumnHeader1 | ColumnHeader2 |
------------------------------
BAPune           |null        |
-------------------------------

Upvotes: 1

Views: 84

Answers (3)

Rahul
Rahul

Reputation: 77926

Try adding FIELDS TERMINATED BY ',' ESCAPED BY '\\' in your query like

LOAD DATA LOCAL INFILE 'D:\\mydatafile.csv' 
INTO TABLE mydb.mytable FIELDS TERMINATED BY ',' 
FIELDS TERMINATED BY ',' ESCAPED BY '\\'
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES
(ColumnHeader1,ColumnHeader2);

Upvotes: 1

Mekap
Mekap

Reputation: 2085

Your \ is not escaped by mysql, you need to escape it to have it in your database. You'll have two choices to do that if you want to only use either your hand or mysql only: Either replace by hand (which is a poor man's way) \ into \\;

Or you could use LIKE pattern [ ESCAPE 'escape_character' ] in an insert statement, thus allowing you to escape everything you'd like.

Upvotes: 0

prcvcc
prcvcc

Reputation: 2230

try replacing all of your \ with \\ on the csv file and see what happens

from https://dev.mysql.com/doc/refman/5.1/en/load-data.html

Backslash is the MySQL escape character within strings in SQL statements, so to specify a literal backslash, you must specify two backslashes for the value to be interpreted as a single backslash. The escape sequences '\t' and '\n' specify tab and newline characters, respectively.

Upvotes: 1

Related Questions