HELP: Importing an Excel file into Access 2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 (e-mail address removed)

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!!!
 
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
 
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
 
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?
 
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.
 
Nope, didnt get past that either.

Klatuu said:
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.
Click to expand...
 
Back
Top