Import from Excel

  • Thread starter Thread starter John
  • Start date Start date
J

John

I am having difficuly with importing data from excel
files into Access.When I run the import wizard all steps
are fine but I only get a partial import of data eg 114 of
260 records.
If I manually insert the rejected data through the form the
data is accepted. I am using Access 2002 and Windows XP, I
have loaded Service pack 3.
 
Do you get an error table after the import, telling you of any errored
records that could not be imported? What are the data in those "missing"
records?

More info, please.
 
Error message 1 is 0 records lost or deleted due to key
violations.
Error message 2 An error ocured while trying to import
the file, the file was not imported.

DB contains names of students, employers, assignments etc.
Currently 1000 students, 800 employers, 450
assignmentswith at least 2 parts each.
approx 7mb
Lost records (not imported) were a combination of text
and numbers.
Any one of the non imported records could be imported
using the individual imput form.
DB was originally written using Access 2000 then
converted to 2002. DB has been functioning well for
almost 2 years. Import wizard was recentlysucessfull in
importing 145 further assignments to table with no
losses.
 
My first inclination is that there is something wrong with the EXCEL file or
its data.

Error message 1 usually doesn't show just the message excerpt that you
posted. It also usually shows additional info re: other possible violations.
If there were no such violations, Error message 1 doesn't get displayed.

Post info re: the table structure (fields and format of those fields) and
some sample data (a few records that work and a few records that don't).
 
Ken,

Sorry for the delay.

There are 10 fields in the table
CompanyID (Key) autoNumber
CompanyName text
Address1 text
Address2 text
Address3 text
City text
ContactPerson text
ContactTelephone text input mask
\(99”)”9009009;0;_
MITORepresentativeID number
Company comments memo

This record was accepted OK

CompanyName Address1 Address2 Address3
City ContactPerson ContactTelephone
MITORepresentativeID CompanyComments
A B Equipment Ltd 25a Hewllett Street
WHANGAREI Garry Raines (09) 4157600 1

A B Equipment Ltd P O Box 23151 Papatoetoe
AUCKLAND P Becker / A Ryan (09)
2622732 16

The following were not

Baymarine P O Box 2206 TAURANGA
Peter Carter (07) 5776005 6
Bayquip Agricultural Ltd 202 Jellico St Te Puke
TE PUKE Brett Congalton (07) 5730320 6
Brian Collins Machinery 105 Bell Road
TAUMARUNUI Brian Collins (07) 8955240 5
Bryant Tractors (1983) Ltd PO Box 78 Whangarei
WHANGAREI Keith Martin (09)
4381319 1


If this format is not adequate I can e-mail

Many thanks

John
 
My first inclination is that ACCESS is trying to import your first "field"
(column) in the EXCEL file into the autonumber field in your table. Because
you have that field in the table, ACCESS will try to match the first EXCEL
column to it, then the second column to the company name field, etc.

Try importing the EXCEL file into a temporary table, and then use an append
query to copy the data from the temporary table into the permanent table.
 
Back
Top