load data infile ... with UTF-8 characters
I do bulk loads using mysqlcmd and the "load data infile" command. I'm using MySQL 5.7 on Win 7.
My command looks like this:
LOAD DATA INFILE 'C:\\Document\\convert\\sfx.tab'
IGNORE
INTO TABLE prod.sfx
CHARACTER SET 'UTF8' -- UTF-8 declared here
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''
(id, id_stamp, @var_leader, @var_description, @var_prices, @var_sfx, @var_sfx_ind)
set
leader = trim(@var_leader),
description = trim(@var_description),
prices = trim(@var_prices),
sfx = trim(@var_sfx),
sfx_indicator = trim(@var_sfx_ind);
The "description" field sometimes has UTF-8 characters. Specifically, the curly left- and right-quote marks. When it does, the data does not load. But if I change those quotes to plain ascii quotes (as in "), it loads fine.
Yes, the table declaration calls for UTF-8.
table column ord data char char char collation
name name pos type max len octet len set name name
---- ------------- --- ---- ------- --------- -------- ---------------
sfx ID 1 int nul nul nul nul
sfx ID_stamp 2 int nul nul nul nul
sfx after_leader 3 varchar 100 300 utf8 utf8_general_ci
sfx description 4 varchar 4000 12000 utf8 utf8_general_ci
sfx prices 5 varchar 1000 3000 utf8 utf8_general_ci
sfx sfx 6 varchar 100 300 utf8 utf8_general_ci
sfx sfx_indicator 7 varchar 100 300 utf8 utf8_general_ci
The odd thing is that other tables with columns that contain the curly UTF quotes ... load just fine!
What's up with that? How do I fix it?