SQL Load Data Special Characters

Question

I'm trying to import data into SQL from a CSV in PHP My Admin so it may be a PHP My Admin problem. The problem i'm having is that some of the columns use special characters for instance:

"Adán, Antonio"

Ends up as just "Ad".

The column structure is as follows:

CREATE TABLE IF NOT EXISTS `players` (
  `player_name` varchar(255) COLLATE utf8 NOT NULL,
  `player_nation` varchar(255) CHARACTER SET utf8 NOT NULL,
  `player_club` varchar(255) CHARACTER SET utf8 NOT NULL,
  `player_position` varchar(255) CHARACTER SET utf8 NOT NULL,
  `player_age` tinyint(2) NOT NULL,
  `player_dob` varchar(32) CHARACTER SET utf8 NOT NULL,
  `player_based` varchar(255) CHARACTER SET utf8 NOT NULL,
  `player_id` int(10) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8;

I'm guessing it's something to do with the character set but mysql.com just suggests to alter the table to characters set utf8 which it already is.

Any ideas how else I can prevent this?

---

UPDATE

Inserting into the database is fine, so i'm guessing it's not to do with my table structure. It seems to be specifically to do with importing from a CSV.

This is the query for load data, as generated by PHP My Admin

LOAD DATA LOCAL INFILE 'C:\\Windows\\Temp\\php21E4.tmp' INTO TABLE `players` FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'.

---

SOLUTION

I discovered the problem. My CSV was encoded with a Western Europe charset. After faffing around for a bit to convert it to UTF8 it imported just fine. Not an SQL problem at all.

Answer 1

Can you track where the truncation is happening, its quite possible that the issue isn't with your DB at all.

Try a simple insert into your DB table from the command line with the special chars and see if it succeeds.

Then try logging the various steps in the import to track where the issue occurs...