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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s