Importing an excel field that contains - data type mismatch

[Guest]

I am importing an excel file as a table to access. One field in the excel
file has both alpha/numeric characters. Access is giving me a "data type
mismatch" and will not import the record with both alpha and numeric
characters. How can I import this field?

 

[Ken Snell [MVP]]

A "bad" feature of ACCESS 2003 and earlier versions...

The problem is that Jet is seeing numbers in the first 25 or so rows of your
EXCEL spreadsheet, so it assigns a number data type to that field, and then,
if it finds a text value in the field in a later row, it errors and won't
import that value.

You can work around this by adding apostrophes to the beginning of the
number values in the EXCEL cells (which tells Jet that the numbers in fact
are text), or you can put a text value (such as what you posted) in the
first row in the EXCEL sheet.

There are ways to make changes in the registry to force Jet to examine all
rows before deciding on a data type, but that probably is overkill for what
you want to do right now.

 

[Guest]

OH MY GOD! Brilliant! I actually thought I was going to go insane from type
mismatches, but once I switched the first 40 rows, it worked. Thanks for
saving my company hours of manual data entry!

Tamsyn Hartlen

 

[Guest]

This works great - thanks!

A "bad" feature of ACCESS 2003 and earlier versions...

The problem is that Jet is seeing numbers in the first 25 or so rows of your
EXCEL spreadsheet, so it assigns a number data type to that field, and then,
if it finds a text value in the field in a later row, it errors and won't
import that value.

You can work around this by adding apostrophes to the beginning of the
number values in the EXCEL cells (which tells Jet that the numbers in fact
are text), or you can put a text value (such as what you posted) in the
first row in the EXCEL sheet.

There are ways to make changes in the registry to force Jet to examine all
rows before deciding on a data type, but that probably is overkill for what
you want to do right now.