Import Error

[kkyork82]

Hello all,

I have the strangest thing occuring when I try to upload data from an excel spreadsheet into an existing table in my database. I keep getting the following error:

"Microsoft Access was unable to append all the data to the table.

The contents of fields in 0 record(s) were deleted, and 3398 record(s) were lost due to key violations."

Seems completely standard, HOWEVER, I have hit no in response to the "Do you want to proceed anyway prompt" and then hit "Finish" again on the Import Wizard and I'll get the same message back but with a different number of records being lost due to key violations.

Any ideas?

 

[AccessMSSQL]

You need to check your Excel Data against the Access table. This error means you have values from Excel going into fields in Access that don't allow that value. For example if your access table has a field that does not allow nulls, but Excel has blank values this will produce an error. Or, if you have a Yes/No column in Access and your excel data is trying to put a text value in that field you will get this error also. Could be a number of other reasons - duplicate values in the primary key, etc.

You need to clean your Excel data before trying to import to the Access table.

 

[kkyork82]

Well, I have no idea what was wrong but I just kept closing out of the error and then hitting finish on the Import Wizard and it took it this last time. I didn't change anything in the spreadsheet - just went through the error message a couple of times. Seems pretty strange. Could my database be corrupt?

 

[jdraw]

That's a possibility. Can you read your imported table now?
Can you do a few queries to see if anything is amiss?

Often you don't just import from Excel to Access because of structured tables. Sometimes it's easier to import to a Holding Table, verify the info, then set up queries to populate various "operational/real" tables.

 

[AccessMSSQL]

I would check the number of rows imported vs the number of rows in Excel. Also, Acces generates an ImportErrors table. You should check to see if it created an ImportErrors table for your last import. This table should give you the row in Excel that failed and the column name.