Importing from Excel into Access getting Type Conversion Errors for specific rows

[sophiereynolds]

I am trying to use Access as a better way to handle test scores from third party software. I am able to export from these software's into Excel and then after cleaning up the data I am trying to import it into Access. I have gone around all the most obvious troubleshooting steps (looking into the data that has the error and making sure its all the same type as Access has assigned it, ect.) I have also noticed while the errors look random the columns are being attributed to random students instead of random column/row errors. I have also looked into this side of the problem and one of the "problem" students would have an error for a score that's, for example, 42. I am really at a loss on how to fix this problem as well as being newer to Access. Any help will be appreciated!

[rpeare]

can you provide a sample of the unchanged excel file. if the file has names in it you can delete those columns but I suspect that even though you are 'cleaning' the data there is a disconnect between data types/some sort of conversion issue that can be handled with vba but I'd have to be able see exactly what's going on to offer a solution. If you have a database already started it would be helpful to have that as well.

[ranman256]

You can get Conversion errors because Excel guesses at the conversion types. (it can be wrong)
So when I import, certain fields can look like numbers but are text, and vice versa.

Before I import I must run a macro Cvt2Txt, that converts my string column by putting a single quote (') in front of everything.
And for Cvt2Num, it validates it is numeric.

This stopped a lot of wrong data types.