I have several csv files in am loading into MySQl using Java. In the Description field I have several Special Characters that are causing the load to fail. I am using LOAD DATA INFILE. This is nested in a for each loop which parses an array of filenames / tables and runs through each combination until it is finished with all the files.
Here is my jdbc connection string where I am passing a definitive collation param/value for UTF8 collation
static String url = "jdbc:mysql://localhost:3306/stage?verifyServerCertificate=false&characterEncoding=UTF8";
Here is my LOAD DATA string.
final String sql2 = ("LOAD DATA INFILE" + filetoEat + "INTO TABLE staging." +tableName + "CHARACTER SET UTF8 FIELDS TERMINATED BY',' ENCLOSED BY '\"\' LINES TERMINATED BY '\n' IGNORE 1 LINES");
Create Table Statement
CREATE TABLE `zmi064_02` ( `Material` varchar(11) CHARACTER SET utf8 DEFAULT NULL, `Material Description` varchar(60) CHARACTER SET utf8 DEFAULT NULL, `MatType` text CHARACTER SET utf8, `MatGrp` text CHARACTER SET utf8, `ClassType` text CHARACTER SET utf8, `Class type Text` text CHARACTER SET utf8, `Class` text CHARACTER SET utf8, `Class Text` text CHARACTER SET utf8, `CharactName` text CHARACTER SET utf8, `CharactValue` text CHARACTER SET utf8, `Plant` varchar(6) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server utf8 character_set_system utf8 collation_connection utf8_general_ci collation_database utf8_unicode_ci collation_server utf8_general_ci
The code is working fine until it comes across a special character like a degree symbol or micro symbol µ within a Material Description . At that point it throws an Exception
Invalid utf8 character string: 'LUG'
The string LUG is followed by a µ symbol. The DB is set to utf8 - utf8_unicode_ci and the column in question is a VARCHAR(60) that holds material descriptions. I have tried using ESCAPED BY '\' but I can't seem to get it working correctly. I have also tried CHARACTER SET UTF8. I have also tried different collation ie, utf8_general_ci to no avail.
Any insight is greatly appreciated