load data query is not working with INSERT/REPLACE option

Question

I am trying to upload data from CSV file into a mysql database table but i am getting error "java.sql.SQLException: Invalid utf8 character string: '' " when using INSERT/REPLACE option in load data query. but the same query works fine without INSERT/REPLACE option.

Query:

Statement  stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);

String  query = "LOAD DATA  INFILE 'D:\\"+flnm+"'  REPLACE INTO TABLE prfl_hntr " 
+ "FIELDS TERMINATED by ',' LINES TERMINATED BY '\n' IGNORE 1 LINES"
+ " (candidate, phone, mailid, skill, texp, rexp, pctc,np);";
stmt.executeUpdate(query);

I have verified the syntax in mysql documentation- https://dev.mysql.com/doc/refman/8.0/en/load-data.html.

Please note that i need to use REPLACE/IGNORE option in the query to eliminate duplicate entries.

Answer 1

You are right the issue is not with the syntax. The issue might be with difference in the characterset between Mysql table and your CSV file. I had a similar problem and resolved it by mentioning the characterset in the query. Try the query given below it should work and make sure your CSV file has data supported by Mysql table.

Query:

String  query = "LOAD DATA  INFILE 'D:\\"+flnm+"'  REPLACE INTO TABLE prfl_hntr " 
+"character set latin1 "
+ "FIELDS TERMINATED by ',' LINES TERMINATED BY '\n' IGNORE 1 LINES"
+ " (candidate, phone, mailid, skill, texp, rexp, pctc,np);";
stmt.executeUpdate(query);

Further you can follow the answer by @RolandoMySQLDBA for this question-: Trying to do LOAD DATA INFILE with REPLACE and AUTO_INCREMENT It worked wonders for me.