help with importing errors

[Chris Barnett]

hello there

i have an access database into which i'm importing data from 2 excel
spreadsheets.

the spreadsheets have exactly the same format -

6 columns with the headings
admin - a unique pupil number
subject - the subject involved
set - the group the pupil is in
attain - a number or letter depending on the subject and attainment of pupil
class - effort grade (text)
home - effort grade (text)
beh - behaviour grade (text)

i have suddenly developed a problem where access is refusing to import all
the "attain" data citing Type Conversion errors in an import error table.

i have tried formatting all the attain column to text data in the excel
sheet before importing but still with no luck.

i'm sure i had a mix of numbers and letters involved when i last did an
import (at the weekend) and have never had to alter any cell formats - so i
would be grateful for any solutions or ideas as to why it is suddenly not
working for me.

ps one of the sheets can be imported error free and currently has no number
data in the attain field. however importing the other sheet brings up the
errors.
i have also tried creating the structure for the table first and fixing them
all as text fields before importing to it but that also doesn't seem to
work.

thanks
Chris Barnett

 

[Joseph Meehan]

hello there

i have an access database into which i'm importing data from 2 excel
spreadsheets.

the spreadsheets have exactly the same format -

6 columns with the headings
admin - a unique pupil number
subject - the subject involved
set - the group the pupil is in
attain - a number or letter depending on the subject and attainment
of pupil class - effort grade (text)
home - effort grade (text)
beh - behaviour grade (text)

i have suddenly developed a problem where access is refusing to
import all the "attain" data citing Type Conversion errors in an
import error table.
i have tried formatting all the attain column to text data in the
excel sheet before importing but still with no luck.

i'm sure i had a mix of numbers and letters involved when i last did
an import (at the weekend) and have never had to alter any cell
formats - so i would be grateful for any solutions or ideas as to
why it is suddenly not working for me.

Just a guess, but I wonder if you had all numbers in the first few
records and the letters only appeared down the list. Access does not check
all the data before importing.

 

[Chris Barnett]

yes you're right - i sorted the columns so it found all letter grades first
and it worked

but is there anyway of making this automatic

i though access & excel worked seamlessly together but even if the excel
cell is text formatted it appears that access ignores that and tries to
import it as number data and refuses to put it into a text field or if
starting from scratch creates the database field as a number setting and
then refuses to import the letter grades.

any ideas?
cheers
chris barnett

 

[Joseph Meehan]

yes you're right - i sorted the columns so it found all letter grades
first and it worked

but is there anyway of making this automatic

i though access & excel worked seamlessly together but even if the
excel cell is text formatted it appears that access ignores that and
tries to import it as number data and refuses to put it into a text
field or if starting from scratch creates the database field as a
number setting and then refuses to import the letter grades.

any ideas?
cheers
chris barnett

For many years I worked with Access 97. As I recall it had the ability
to build and save import "scenarios" that would be just want you need. I
don't see them in my XP version. I used them a lot back in those days, but
I don't have a current need and I don't see a way to do the same thing in
XP. I suspect it is there, but I don't know where. Sorry I could not be of
more help.