Error While Importing Data from Excel

Question

I have been importing data from Excel to Access database I have imported without any issue many times before, all of sudden today I am getting this error, some of the record will be lost.

I have checked if I am violating referential integrity rule, primary key duplicates, or data type of the field i am importing does not match table field data type in access.  I did not find any issue with any of the three.

Just to see if the records will be really lost, i copied my database and import the same file in copied database, when warning was displayed i accepted to go ahead anyway.  surprisingly I did not lost any of the record as the warning said, i am not sure what is going on the message being displayed.

Did anyone encounter a similar issue? what has changed since the last time i imported a file, I gone up from Microsoft Office 2010 to 2013. Any suggestions

Thank You

Answer 1

What was the exact error message?

Answer 2

Well the Error message is clear, you have values in your import file that would result in duplicate values in constrained fields in the destination table. So you need to look at the table design and see what constraints exist and then look at the data to see what duplications might be occurring.

Answer 3

Tom,

As you have mentioned in your response, there might be fields where the character limit might have exceeded the number of characters allowed in the Access field. I have check that, all my fields allow the maximum of 255 characters for the text and data I am importing none of the cells exceed that limit.

Thank You

Answer 4

The error message is

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

just for reference I have attached a screenshot of my table relationship diagram and I am importing the data to TBLINVOICE, primary key values in the records being imported are unique. I have check for them already.

As I mentioned in my previous post, just for trying sack I did accept the warning to loose record, but when i reviewed the total entries added to the table, none was lost. However, the error claims that 142 will be lost.

Thank You

Answer 5

Please quote any error messages VERBATIM.

Reading between the lines I think you may have some truncation. Say you have a text(50) field and Excel has 51 chars in the corresponding field.

Sometimes it's not easy to see that Excel has more data than you think; for example a cell could have some text followed by a carriage return / linefeed and additional text. The extra text would appear on the second line and not be visible unless you expand the row height.