CODEWITHSUNDEEP
phpmysql
Earon
Earon

Reputation: 823

IGNORE INTO Mysql not ignoring record

Here is my sql code to load csv file into database but i have lots of duplicate code generated by perl script. I want to ignore duplicate records while inserting into DB and it's work but every time it inserts one duplicate record you can see in screenshot.

load data local infile '" . public_path() . "/temp_data.csv' IGNORE INTO TABLE temp_data fields terminated by ','
                      enclosed by '\"'
                      lines terminated by '\n'
                      IGNORE 1 LINES
                      (state, city, category, business, contact, address, phone, website, email)";
            $connection = mysqli_connect('localhost', 'root', '0r@ng3cr33d', 'yourserv');

enter image description here

Table Scheme :

enter image description here

enter image description here

Why ? i am using unique key on column email but still it's adding duplicate.

One thing i noticed when load csv into database it's picking \r word in some records, anyone know how i can ignore that ? maybe that's why it inserting second one.

Example :

enter image description here

1 : [email protected] 2 : [email protected]\r

Thanks

Upvotes: 0

Views: 42

Answers (1)

Mandeep Gill
Mandeep Gill

Reputation: 4907

Just use this :

lines terminated by '\r\n'

Upvotes: 1

Related Questions