4

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;

Show variables

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

  • Add the charset clause to your LOAD DATA statement. – Rick James Jun 7 '16 at 15:11
  • It is part of my load data string, I edited my post and added the LOAD DATA INFILE string.. Thanks – apesa Jun 7 '16 at 15:29
  • 1
    Can you get the hex of the µ in the input. If you get B5, then it is latin1; if you get C2B5, then it is utf8. – Rick James Jun 7 '16 at 20:51
  • Thanks a lot for that, it turns out to be UTF-8 but i never thought to check. This data comes out of a Unicode SAP environment in Spain so it could have been. – apesa Jun 7 '16 at 23:02
  • 1
    Sorry. I have been struggling for years with charset issues. I see 5 distinct symptoms. Yours is different in that it involves LOAD DATA, not INSERT. Still, the only cause for truncation that I have found is the wrong encoding in the source string. – Rick James Jun 8 '16 at 4:43
0

I figured that I would answer this now that I found the solution. Because I am using Java to run the LOAD DATA INFILE via JDBC the JDBC driver seems to be checking the collation at the DB and not the actual table being loaded as it is parsing the file. So you can't have the DB set to UTF-8 and have a Latin collated table as you would be able to do with an INSERT statement. I had tried to set the Table collation as Latin and even had the field in question Latin, but until I changed the entire DB to Latin it was failing. The CSV files are large so checking every char in question is not easy, but I was catching the Exceptions in Java and was able to determine that the error was generated by the JDBC driver and was complaining that "Character at line xx is not a UTF-8 character" Running in Debug allowed me to see more details.

I then concluded it must not be looking at the Latin collated table it would be filling, but was looking at the DB which was still set to UTF-8. Changing the DB to Latin was all I needed to do.

I want to thank @Rick James for his help, I hope this will help others in the future.

Pat

  • 1
    Hmmm... The charset of the database is used only as the default when creating a table in that database. Similarly, the charset for the table is just a default for the columns in case you do not explicitly state a charset for them. And... – Rick James Jun 13 '16 at 0:06
  • 1
    The bytes in the client (the CSV files) do not need to agree with the charset of the columns they go into. But, you must establish what charset encoding (latin1 or utf8, etc) those bytes are in. This is done via the connection or load data or set names. – Rick James Jun 13 '16 at 0:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.