Another Access Trick

So I’m busy importing survey data (an Excel file) into our SQL Server via Access, and I my queries are turning out screwy results. I finally figured out why.

A field that should have been imported as a varchar (i.e. text) is instead being imported as a floating point number. Access was assigning the wrong data type to the field, and then dumping whatever data didn’t fit that data type. That’s because the first few dozen rows have zeros in that field. The text responses don’t turn up until later. Evidently Access looked at the zeros, decided it was a float, and then in the process of importing just skipped any data that wasn’t a float.

When I sorted the file so that text values were at the top, everything comes in fine.

Still an annoying bug, though.

Related posts:

1. Another trick for bypassing unknown error on Access data import
2. Another trick for avoiding an Access import error
3. Avoiding errors when importing data into Access.
4. Access data import is idiotic
5. Access import trick: Make sure columns are wide enough

Tagged as: Access, errors, import data