how to load csv file content to mysql database and avoid duplicate entry?

Question

What I was trying to do is to load a csv file into the database. But I am having a problem because of duplicate entry.

I am using PHP script to load the file.

$sql = "LOAD DATA INFILE '" . $target_path;
        $sql.= "' INTO TABLE dummy ";   
        $sql.= "COLUMNS TERMINATED BY ','";
        $sql.= "OPTIONALLY ENCLOSED BY '\"'";
        $sql.= "ESCAPED BY '\"'";
        $sql.= "LINES TERMINATED BY '\n'";
        $sql.= "IGNORE 1 LINES;";

Database Table Structure:

id | firstname | lastname

CSV File content..

id | firstname | lastname

1  John Doe
2  James Carry
3  Luke Borrow

As expected 'id' is the primary key. Is there a way to skip values that violates primary key constraint on the fly?

@skrilled: there is no ON DUPLICATE KEY syntax for LOAD DATA INFILE, it's not a regular INSERT query. Even on INSERT queries, you could just use INSERT IGNORE if you don't plan to update anything. — Wrikken
– Wrikken, Commented Aug 13, 2013 at 23:00

Wrikken · Accepted Answer · 2013-08-13 22:58:16Z

6

LOAD DATA INFILE 'somefile' IGNORE

From the manual:

If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped.

answered Aug 13, 2013 at 22:58

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

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

Collectives™ on Stack Overflow

how to load csv file content to mysql database and avoid duplicate entry?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related