HELP: Importing an Excel file into Access 2000

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Goe - 12 Aug 2005 05:30 GMT

Hello,

I cannot import all data in an Excel file into an existing table in Access.
The error msg says that 0 records were deleted & 0 records were lost due to
key violation. When i ignored this msg and proceeded with import, some
records were missing. I couldn't identify any similarities between the
missing records...

Excel table looks like this:

Number Name Phone Email
723456 XYZ 658 5686 abc@baedi.com

The Number column is in Numeric format, while the rest are in Text format. I
even added a ' in front of all text entries, e.g. '658 5686.

The Access table has an additional AutoNumber ID field (PK) and the rest of
the field names are those of the Excel table. Data Type for Number field is
Number (Long Integer), while the rest are Text. I'm quite sure the Field size
for Text is not the source of the problem; they are at 255.

Please help!!!

Thank you very much!!!

Reply to this Message

Nikos Yannacopoulos - 12 Aug 2005 07:56 GMT

Do a copy in Excel. and paste in your Access table manually. This way,
failing records will be stored in a table called Paste Errors, so at
least you'll know which records failed, and maybe you'll be able to
recognize a pattern. If not, you can start to enter one of the failed
records manually, so you'll identify the gui;ty field enrty.

HTH,
Nikos

Reply to this Message

Klatuu - 12 Aug 2005 14:35 GMT

The problem here is Nulls in the Excel spreadsheeet. If an Excel Cell is
formatted as General ( The Default) and no value has been entered into it,
Access will see it as Null when importing. This will cause the behaviour you
are experiencing.

The very best way to move data from an Excel sheet to Access is to create a
table with the structure you need. In addition to getting the data in
cleaner, it saves spaces because importing a spreadsheet uses the default
length for all text fields.
Then, you will want to link the spreadsheet as a table. Create an Append
Query that will copy and format the data for the destination table.
I recommend that all numeric fields coming from the Spreadsheet be wrapped
in the Nz function to avoid the Null problem. So, the procedure is:

1. Delete existing data in the destination table'
2. Link the spreadsheet as a table
3. Run the Append Query to copy the data
4. Delete the link to the spreadsheet

> Do a copy in Excel. and paste in your Access table manually. This way,
> failing records will be stored in a table called Paste Errors, so at
[quoted text clipped - 4 lines]
> HTH,
> Nikos

Reply to this Message

Timber910 - 18 Aug 2005 01:29 GMT

Hello Klatuu,

I tried the 1 - 4 steps you have listed below. I'm having the same thing
happen on a database I'm working on. I was able to get all the data copied
into the table but the autonumber feature i have in the very first colume is
not numbering anything it's just blank. Is their a way I can get the auto
number to actually do what I want it to do?

> The problem here is Nulls in the Excel spreadsheeet. If an Excel Cell is
> formatted as General ( The Default) and no value has been entered into it,
[quoted text clipped - 23 lines]
> > HTH,
> > Nikos

Reply to this Message

Klatuu - 18 Aug 2005 14:41 GMT

I was concerned about that, but I did not have a table with an autonumber
field I could test. Is the autonumber field included in your append query?
If it is, try excluding it from the query and see what happens. Post back,
because I would like to find how to get past that problem as well.

> Hello Klatuu,
>
[quoted text clipped - 31 lines]
> > > HTH,
> > > Nikos

Reply to this Message

Timber910 - 18 Aug 2005 16:01 GMT

Nope, didnt get past that either.

> I was concerned about that, but I did not have a table with an autonumber
> field I could test. Is the autonumber field included in your append query?
[quoted text clipped - 36 lines]
> > > > HTH,
> > > > Nikos

Reply to this Message