I have a MyISAM table with a MailTo column. The column is varchar(100), Column CharSet: utf8
I have a .txt file that I am trying to import with 'load data infile'
The file contains one row with a value of 'NU¥EZ' for the MailTo column.
When I try to load the file, I get error message: "Incorrect string value: '\xA5EZ LU...' for column 'Mailto' at row 1"
This is referencing the '¥EZ' of 'NU¥EZ'. The ''¥' is being represented by the \xA5.
Same problem if I change the string to 'NUÑEZ': "Incorrect string value: '\xD1EZ LU...' for column 'Mailto' at row 1"
The 'Ñ' is '\xD1'
Now if I COPY the string from the .txt file and edit the table in MySQL Query Browser and paste the string into a new row, MySQL accepts it without a problem.
Why is the load statement failing? And how can I load the file?!
I am also annoyed that I am apparently dealing with 2 different code pages: I suspect the data was originally stored with some us / western / latin code page where N with tilde is \xa5; and then I have a database table using utf8 where \xa5 is a yen symbol, whereas \xD1 is the 'N with tilde'. Is there a more elegant way for me to import without having to do a global replace to change the .txt file (i.e replace all \xA5 with \xD1)?