Duplicate Keys


 
Thread Tools Search this Thread
Top Forums Web Development Duplicate Keys
# 1  
Old 11-19-2014
Duplicate Keys

I am trying to insert csv data into a table
Code:
mysql> load data infile '/var/www/PLU.csv' into table Food2 fields terminated by ',' enclosed by '"' lines terminated by '\n' ;
ERROR 1062 (23000): Duplicate entry '4014' for key 'PRIMARY'

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| PLU         | char(6)     | NO   | PRI | NULL    |       |
| Item        | varchar(30) | YES  |     | NULL    |       |
| Type        | varchar(30) | YES  |     | NULL    |       |
| Size        | varchar(10) | YES  |     | NULL    |       |
| InStock     | int(5)      | YES  |     | NULL    |       |
| OrderPoint  | tinyint(5)  | YES  |     | NULL    |       |
| OPFlag      | tinyint(1)  | YES  |     | NULL    |       |
| StockWanted | int(3)      | YES  |     | NULL    |       |
| Price       | float       | YES  |     | NULL    |       |
| WeightFlag  | tinyint(1)  | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

Here is a sample of the csv data..

Code:
3615,"APPLES","Civni",,,,,,,
3630,"APPLES","Co-op 43",,,,,,,
4104,"APPLES","Cortland","Small",10,5,0,15,0.25,1
4106,"APPLES","Cortland","Large",12,5,0,15,0.30,1
4105,"APPLES","Cox Orange Pippin",,,,,,,
4107,"APPLES","Crab",,15,7,0,25,0.15,0

The troublesome primary key has been removed, the table dropped and recreated, the csv file has been recreated, all with no success.
# 2  
Old 11-19-2014
I did not have this problem. This implies something is in the table.
I created a testcase of your scenario:

Create these 3 files

file1: testcase (created DB, table and inserts data into file)
Code:
#testcase
set -x
DB="ZYX"
DIR="/var/lib/mysql/${DB}"
FILE="${DIR}/x.csv"

mysql <<EOD
create database ${DB};
use ${DB};
CREATE TABLE Food2 (
PLU CHAR(6) ,
Item varchar(30) ,
Type         varchar(30) ,
Size         varchar(10) ,
InStock      int(5)     ,
OrderPoint   tinyint(5),
OPFlag       tinyint(1),
StockWanted  int(3)   ,
Price        float   ,
WeightFlag   tinyint(1) ,
 PRIMARY KEY (PLU)
);
EOD
cat <<EOD2 > ${FILE} 
3615,"APPLES","Civni",,,,,,,
3630,"APPLES","Co-op 43",,,,,,,
4104,"APPLES","Cortland","Small",10,5,0,15,0.25,1
4106,"APPLES","Cortland","Large",12,5,0,15,0.30,1
4105,"APPLES","Cox Orange Pippin",,,,,,,
4107,"APPLES","Crab",,15,7,0,25,0.15,0
EOD2
mysql << EOD3
use ${DB}
load data infile '${FILE}' into table Food2 fields terminated by ',' enclosed by '"' lines terminated by '\n' ;
EOD3

File 2: testcase.show #shows data in table
Code:
#testcase.show
set -x
DB="ZYX"
DIR="/var/lib/mysql/${DB}"
FILE="${DIR}/x.csv"

mysql <<EOD
use ${DB};
select * from Food2;
EOD

testcase.cleanup #removes DB and table and allows you to retest

Code:
#testcase.cleanupset -x
DB="ZYX"
DIR="/var/lib/mysql/${DB}"
FILE="${DIR}/x.csv"

rm ${FILE}
mysql <<EOD
use ${DB};
drop table Food2;
drop database ${DB};
EOD

To test

Code:
chmod +x testcase*
./testcase
./testcase.show
#try re-importing the data, it will fail
./testcase.cleanup

I was able to insert this csv file with no problems.
I could not recreate your error.
You state that you dropped the table, but you don't show the databases or the tables in your mysql subsystem. This testcase insures there is nothing in the table.
# 3  
Old 11-19-2014
Quote:
Originally Posted by Meow613
ERROR 1062 (23000): Duplicate entry '4014' for key 'PRIMARY'
...
The troublesome primary key has been removed, the table dropped and recreated, the csv file has been recreated, all with no success.
Double-check your csv file for this record, eg.
Code:
grep ^4014, file.csv

# 4  
Old 11-20-2014
Thanks to all.

I took a brute force approach. [as per Dr. Google]

Export to LibreOffice calc.

MySQL Export Table to CSV

Code:
SELECT orderNumber, status, orderDate, requiredDate, comments 
FROM orders
INTO OUTFILE 'C:/tmp/cancelled_orders.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

Modify above code as needed.

Import into LibreOffice Calc.

Modify as needed to remove the duplicates.

Truncate the table w/ the duplicates.

Import the csv file.

Done

BTW Is there a spell checker in any of the reply boxes?

---------- Post updated at 09:28 AM ---------- Previous update was at 12:17 AM ----------

To all -

My apologies.

I was not trying to test anyone with my post. Your answers are excellent! I was totally confused prior to my post and also felt lost. Suddenly it hit me why not try the method that I mentioned. I have approximately 1500 lines in my spreadsheet. What I suggested would not work with much larger spreadsheets.

Again, sorry.
# 5  
Old 01-12-2015
The code works well.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Find duplicate values in specific column and delete all the duplicate values

Dear folks I have a map file of around 54K lines and some of the values in the second column have the same value and I want to find them and delete all of the same values. I looked over duplicate commands but my case is not to keep one of the duplicate values. I want to remove all of the same... (4 Replies)
Discussion started by: sajmar
4 Replies

2. Shell Programming and Scripting

Extract values of duplicate keys

I have two questions that are related, so it would be great if you can help me with both! Question1: I have a file A that looks like this: a x b y b z c w I want to get something like: a x b y; z c w Given that a,b,c has no spaces. But the other letters might contain spaces. ... (2 Replies)
Discussion started by: Viernes
2 Replies

3. Solaris

help with SSH keys

Hello, I could use some help with my ssh keys and agent. This is the issue. I have 2 different UNIX systems at work. One is the normal Solaris servers with my uid being the same throughout all the servers. I now have a different system for my desktop. A contractor came in and installed some SUN... (0 Replies)
Discussion started by: bitlord
0 Replies

4. Shell Programming and Scripting

Find duplicate based on 'n' fields and mark the duplicate as 'D'

Hi, In a file, I have to mark duplicate records as 'D' and the latest record alone as 'C'. In the below file, I have to identify if duplicate records are there or not based on Man_ID, Man_DT, Ship_ID and I have to mark the record with latest Ship_DT as "C" and other as "D" (I have to create... (7 Replies)
Discussion started by: machomaddy
7 Replies

5. Red Hat

ProxyCommand with different keys

Hello all, simple network problem: host1 -> jumphost -> host2 alias ss='ssh -t -q user2@jumphost -q -t -t ssh -l user2 ' ss host2 works like a charm but scp and scripts don't cooperate very much (normal). My ssh key (user1) is already deployed on jumphost but NOT on host2 as i'm... (0 Replies)
Discussion started by: maverick72
0 Replies

6. Shell Programming and Scripting

What are public keys in ssh and how do we create the public keys??

Hi All, I am having knowledge on some basics of ssh and wanted to know what are the public keys and how can we create and implement it in connecting server. Please provide the information for the above, it would be helpful for me. Thanks, Ravindra (1 Reply)
Discussion started by: ravi3cha
1 Replies

7. UNIX for Advanced & Expert Users

obtain duplicate keys in csv file

Hi, having two csv files, both sorted, by key (column1), f1 containing duplicate keys and f2 containing no duplicate keys, how can I obtain all rows from f1 with the keys listed in file2? Example: f1 is: k1,gsj01fd k2,vi982cj k2,1fjk01e k3,81kjfds k4,sd9dasi f2 is: k2 k3 and I... (3 Replies)
Discussion started by: oscarmon
3 Replies

8. UNIX for Dummies Questions & Answers

SSH keys

Hi everyone, i wanted to generate ssh keys so that i can include the public key in the remote sever, so that for subsequent logins, i can do away with the keying in of the password. I consulted the man ssh-keygen man pages. "..Normally each user wishing to use SSH with RSA or DSA... (1 Reply)
Discussion started by: new2ss
1 Replies

9. Shell Programming and Scripting

Hot Keys

I am a new user, using Unix in a DOS window. Can I set up Hot Keys to run a script? Example - A12.1.13.15 aaaaBbbbCccc Thank you, cwtlr (8 Replies)
Discussion started by: cwtlr
8 Replies

10. UNIX for Dummies Questions & Answers

arrow keys / special keys

how to use the arrow keys in shell scripting. is there any special synatax / command for this. i just want to use the arrow keys for navigation. replies appreciated raguram R (3 Replies)
Discussion started by: raguramtgr
3 Replies
Login or Register to Ask a Question