Why is some of my data lost when importing from Excel to Access?

[Guest]

You've been such help -- hope you can help again.

I am importing a spreadsheet with 700+ entries from Excel to Access. The
tables in Access are predefined and I have thoroughly checked the Excel
spreadsheet to ensure compatibility.

When I import it, I get roughly the error message:
MS Access was unable to append all the data to the table.
The contents of fields in 0 records were deleted and 2 records were lost due
to key violations.

If I allow the import and open it, 500 of my records are missing. I've
reviewed the Excel entries in a representative sample of those records and
can't figure out
(1) what is different about them that is preventing the import, and
(2) why the error message is wrongly claiming only 2 were lost.
I haven't had this problem with the other tables.

Please help? I'm so close to done, this is killing me.

Shannon McElearney, MD

 

[Guest]

The problem is most likely how you have your Access table defined. Go
through each field and be sure that Allow Zero Length = Yes and Required =
No. If you have any fields identified as Key Values, then you may have to
set Allow Duplicates = Yes. That is usually the problem.

The other issue that is not obvious, is Excel formatting. If you have a
cell in Excel that is formatted as General and nothing is entered in it, the
Access sees it as Null. It is always best to be sure that the columns in
Excel have a format that is compatible with the corresponding field in
Access. However, if users are doing any copy/paste operations to the Excel
sheet prior to your import, you should reformat before importing. Remember,
that in Excel, when you copy and paste, the target cell takes on the format
of the source cell unless you do a paste/special/values.

 

[Guest]

It's all appropriately formatted and the only required field is the primary
key, which I know is unique. I honestly can't explain why some are being
imported and some aren't. It's the same ones everytime and I can't identify
anything that is different about them.

The data set was created in SAS and exported from there to Excel, but again,
I've checked each of the fields and there are no strange hidden zeros or
anything I can identify.

 

[Guest]

The only other thing I can think of is the Excel sheet may be corrupt. I had
this happen recently. I have a routine that reads a specific range in a
specific sheet in a multi sheet workbook and compares it to an Access table
to validate the data. It had been working with no problems, but all of a
sudden, It started blowing up on me. After a lot of research, we found the
Spreadsheet was actually corrupt and causing the problem.

I think this is a rare but possible event. I would still examine the
formatting carefully as this is the usual cause of this behaviour.

 

[John Nurick]

Hi Shannon,

Whereabouts in the Excel table are the records that aren't being
imported? Is there a common factor there? If you sort the Excel table on
another column, does it change the records that aren't imported?

Have you tried saving the worksheet to a CSV file and importing that?
This is often a way of avoiding problems importing directly from Excel.