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.

LOAD DATA LOCAL INFILE '/home/xxxxx/conf.csv' INTO TABLE configuration FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS; — Mr code.
– Mr code., Commented Nov 21, 2018 at 11:26
Possible duplicate of Using "LOAD DATA LOCAL INFILE" in Java — Mr code.
– Mr code., Commented Nov 21, 2018 at 11:33
Hi hussain there was a typo in question. The problem is occuring when i am issuing command with REPLACE option. The query works fine without REPLACE option as suggested in the post in the link. — pmgowda
– pmgowda, Commented Nov 21, 2018 at 11:49

pradyu · Accepted Answer · 2018-11-24 05:00:37Z

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.

answered Nov 24, 2018 at 5:00

pradyu

918 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mr code. · Accepted Answer · 2018-11-21 11:27:10Z

0

LOAD DATA LOCAL INFILE '/home/xxxxx/conf.csv' INTO TABLE configuration FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

try this once.

answered Nov 21, 2018 at 11:27

Mr code.

3072 silver badges18 bronze badges

1 Comment

pmgowda Over a year ago

Hi hussain, Please take into account that i need to use either IGNORE or REPLACE option because am trying to avoid duplicates when loading data. Also please note that this question is not a duplicate because this issue is not addressed in other post.

Collectives™ on Stack Overflow

load data query is not working with INSERT/REPLACE option

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related