Load Data Infile Error

[The Mask]

Hi,

When I try to load data from a text file, I get an error suggesting duplicate keys, however I have checked the contents of the file and all are definitely unique.

Any ideas please ?

The error is as follows:

LOAD DATA INFILE 'c:\migration\mm\sql\ADDRESS.TXT' INTO TABLE ADDRESS

Line no.:1352

Error Code: 1062 – Duplicate entry '' for key 1

Thanks

[peterlaursen]

Quote:

I get an error suggesting duplicate keys

No – you get an error suggesting dublicate ENTRIES for a key! The error message is a MySQL error and not a SQLyog error. Line no. 1352 must have a dataset that is allready in the database as referred to by “key 1” (probably a PK)

Do you LOAD into an empty table? (EDIT: file >> table)

Another reason could be some mismatch occurring due to existence of some special character like ' ” % or _ in the dataset and incorrect use of escaping within strings.

You might find some useful hints here:

http://www.webyog.com/faq/28_73_en.html

For further help we will need to see

1) that line 1352 and your DELIMITED, ENCLOSED and ESCAPED settings with the LOAD DATA INFILE statement.

2) create statement for the table – expecially how that 'key 1' is defined

[polka4ever]

Since this is a LOAD DATA LOCAL INFILE thread – I wanted to ask a question as well. I am using this to load up some data – and I get a message that states, (in the mySQL Command Line Client):

Query OK, 397 rows affected (0.00 sec)

Records: 412 Deleted:0 Skipped: 15 Warnings: 0

Where is the log for this located? I have searched locally (especially under C:Program FilesMySQLMySQL Server 4.1) – and I do not see any logs for this. Do I need to turn logging on? If so, how do I do so? I'd like to see exactly which files were skipped and WHY.

~polka4ever

~p0lkag1rl

---

making a note to this: apparantly I can use the command “show warnings;” at the command line to show all of the warnings related to this data load – but I'd actually like to be able to see the “skipped” records. Still looking for this info, but I thought this might help point me and others in the right direction, if others are looking for this info as well.

[peterlaursen]

@polkaforever

I believe that most likely the reason for the skipped rows is that some data do not match the columns definition. Such as inserting 'a' in a numerical field. The error could arise because of error in escaping.

You will have to find some sample rows yourself that don't import. Then if we get

* the data

* the LOAD DATA statement

* the create statement for the table

we might be able to get closer. But not with the information that you give her.

[Author]

Posts