load data infile ... with UTF-8 characters
Posted by: Bruce Malmat
Date: September 05, 2015 05:42PM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
load data infile ... with UTF-8 characters
8580
September 05, 2015 05:42PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.