CODEWITHSUNDEEP
ms-access
ramesses
ramesses

Reputation: 74

Importing text file to Access database in Windows 7

I am trying to import data from a text file to Access 2007 and 2010, delimited by vertical bar. I use Import Text Wizard of MS Access, but when I try to import it after choosing appropriate delimiter (preview looks fine), I get the following error:

The changes you requested to the table were not successful because they would create duplicate values in the index, 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.

There are no primary keys, nor relationships, as it is just a text file. I was able to insert this text file in Windows XP, but the problem arises in Windows 7. I was able to successfully export the data into Excel. I also set Indexed to No in Field Options of Import Text Wizard, but that didn't help either.

Any help would be greatly appreciated, as I couldn't find any useful info anywhere.

Edit: I tried inserting into new and existing tables, even a new database (accdb and mdb format), every time I get the same error.

Edit2: I opened the text file in WordPad, and saved it again as txt file, the Access didn't generate any error. The previous txt file didn't show the new lines, but Access was still aware where each record ends, and preview looked fine. The new text file specifically shows new lines (each record separately). If someone has a suggestion about how to overcome this issue without creating a new txt file, please let me know

Upvotes: 0

Views: 760

Answers (1)

Andre
Andre

Reputation: 27634

I don't think there is anything you can do except convert the file to Windows CRLF format.

Although I wouldn't use WordPad for this, but Notepad++.
It has an explicit command for this: Edit -> EOL conversion, and you can be fairly sure that it won't change anything else in your file (I wouldn't be so sure about WordPad).

Actually most text editors that are more sophisticated than Notepad have a command for this, I think. :)

Upvotes: 2

Related Questions