Importing Excel data into Access

Question

When appending data from an Excel file into an Access table, I get the following message:

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

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

*If data was deleted, the data you pasted or imported doesn't match the field data types or the FieldSize property in the destination table.

Do you want to proceed anyway?

Unfortunately, if I respond 'Yes', Access does not then produce an ImportErrors table in which I can look up which fields and which rows are causing the problem.

Is there a way round this, please? e.g. is there a trick to getting Access to produce that errors table?

Answer 1

The table that you are trying to import your data into has established integrity rules and possibly relationships to other tables that your data seems to violate.  You will need to determine what fields have unique indexes established, required data established, etc., and look over the incoming data to see what may be in violation. 

What you can do is import your data into a new table first.  Then you can create queries to review your data for issues including duplicate values, duplicate records, null values, and/or mixed data types or incorrect data types. 

As you correct your issues, you can try to append the data from your new table to the target table.  If you continue to get errors, go back and continue breaking down and correcting the issues one at a time.

Answer 2

That's useful RunningManHD, thank you. I have checked all the fields in the target table; most are not indexed but three  of them are labelled as 'Indexed / Yes (Duplicates OK)' which suggests that should not be a problem (?), and there are none that are 'required' fields. 

I will follow your advice and import the data into a new table.

I'm still mystified as to why Access produces an ImportErrors table in some instances and not others! That table was very helpful for finding the data type violations etc.

Answer 3

Sorry, I'm not sure that I understand. Could you explain a little more, please? I'm a relative novice! Thanks.

Answer 4

It sounds like you tried to add records that had duplicate data that violated the index values of the table. Check for fields that that are set to unique.