Importing Excel into Access Database (1 Viewer)

50gumby

New member
Local time
Today, 11:21
Joined
Nov 6, 2019
Messages
6
I've got a spreadsheet with the same field headers in Access and Excel.
I go through the import process under the External Data tab.
I get an error when I try to complete - seems to be a well-recognised error.

When I compare the imported records in Access to the records in Excel, there is one record that has not been imported.
I can't see any settings that would prevent this one record from being imported.

Any secrets about this function?

Many thanks - just joined here and looking forward to getting to know you.
 

50gumby

New member
Local time
Today, 11:21
Joined
Nov 6, 2019
Messages
6
OK - been playing with it.
I import to a NEW table and there is no problem whatsoever.

Why is this?

Is there a problem with this function?
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 01:21
Joined
Jan 14, 2017
Messages
18,186
Welcome to AWF.
Unfortunately we're not mind readers so you need to tell us what the error is.
However my guess is that the record wasn't imported because it would cause a duplicate in a file where that isn't allowed e.g. a primary key field.

I typed this before post #2 appeared. Its even more likely that duplicates were involved.
 

50gumby

New member
Local time
Today, 11:21
Joined
Nov 6, 2019
Messages
6
Many thanks for that.
Is there any function that informs you which record is the problem child?
The record I tracked down doesn't seem to have any duplicates within the list.
 

isladogs

MVP / VIP
Local time
Today, 01:21
Joined
Jan 14, 2017
Messages
18,186
You need to do a bit of work for this.
Whilst there is a find duplicates query wizard, that only works where the table already contains duplicates.

Possible approaches
1. Check which fields in the table do not allow duplicates then inspect those fields in your import spreadsheet.
2. Create a linked table using your spreadsheet. Now create an unmatched query on the two tables using the wizard. This will show all records in your spreadsheet that aren't in your table. In other words, records not imported.

As previously stated it would help if you showed a screenshot of the error message. As you have less than 10 posts, you'll need to zip it
 

50gumby

New member
Local time
Today, 11:21
Joined
Nov 6, 2019
Messages
6
I tried to post the error message but am not having any results.
So below is the text.

Microsoft Access was unable to append all the data to the table.
The contents of fields in 0 record(s) were deleted, and 92 records were lost due to key violations.
If data was deleted, the data you pasted or imported doesn't match the data types or the field size property in the destination table.
If records were lost, either the records you pasted contain primary key values that already exist in the destination table, or they violate referential integrity rules for a relationship defined between tables.
Do you want to proceed anyway?
 

isladogs

MVP / VIP
Local time
Today, 01:21
Joined
Jan 14, 2017
Messages
18,186
Thanks.
Exactly as I thought.
There are 92 records where the primary key field would be a duplicate of values in existing records. This causes key violations so they aren't imported.

Originally you said only 1 record wasn't imported so I assume 91 were imported. Then you tried to reimport and Access helpfully prevented duplication. In other words it behaved as its intended to do.
 
Last edited:

50gumby

New member
Local time
Today, 11:21
Joined
Nov 6, 2019
Messages
6
I already know there was one record that didn't get imported and I can't see anything wrong with it.
 

50gumby

New member
Local time
Today, 11:21
Joined
Nov 6, 2019
Messages
6
The one record that didn't get imported has the value 202 in the Primary Key field. No other records contain this value.
The data type is Short Text.
 

isladogs

MVP / VIP
Local time
Today, 01:21
Joined
Jan 14, 2017
Messages
18,186
Did you try doing what I suggested before?

Otherwise, we need more information to help you find a solution
Can you upload a stripped down copy of your database and the Excel file? Zip it first
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:21
Joined
Feb 19, 2002
Messages
42,981
I already know there was one record that didn't get imported and I can't see anything wrong with it.
Excel may be fooling you as to the actual contents of the field. Since the record imported when you imported into a new table, the problem may be the data type. Check the data type of each column in the new table vs your pre-defined table.
 

Users who are viewing this thread

Top Bottom