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

 

[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