Text Import Error: "...not successful because they would create duplicate values..."

[neadbecker]

I have a file exported from an accounting system that has no extension. The type just says "file". When I had Windows XP, I added the .txt extension (in the file name itself without opening) and was able to import the file into Access without issue. With Windows 7, it seems that this practice leads to the following error when the file is imported to Access:

"The changes you requested to this table were not successful because they would create duplicate values in the index, the primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

I know that none of the fields of the destination table are indexed and there is no primary key. The table has not changed in between the windows conversion. In addition, I get the same error if I import into a new table.

My current work around is to open the .lis file in Excel and save it as text with no further modification to the file. When this is done, the file imports flawlessly. I would like to avoid this as the file is very large and it takes some time to open.

I've searched this out quite a bit and haven't found a very good answer, other than something related to the primary key or index (which again i do not have).

Any help or suggestion is greatly appreciated.

 

[jasmith4]

There has to be something not allowing dupes: the message says "index, primary key or relationship".

1. What do you see if you open the destination table in Design mode and do View / Indexes? All indices, including any primary key, would be here.

2. What does Tools / Relationships show?

Finally, I remember from decades ago that .LIS files from ages-old accounting systems have what are called "carriage-control characters" at the beginning of every line -- see http://publib.boulder.ibm.com/iseries/v5r2/ic2924/books/c415713515.htm#HDRTBAN for a list. When you import directly from the .LIS file, you're bringing in these characters, but when you open the file in Excel and re-save it, Excel might be getting rid of them for you. Can you open that .LIS file in a hex editor or something pretty raw so you can see them?