Type Conversion Failure

[mkdrep]

I am trying to import an Excel spreadsheet into an Access 2007 database. Each time I import, Access creates a 'type conversion failure' error database.

I set up the first field in my .db to be a TEXT field because the Field will have both numeric and text characters. The field name is [estimate number] with numbers such as 656111 or 65611A being imported. All the fields with the numeric and alphabetical combination (i.e. 65611A) are not being imported (which results in the creation of the error db). :banghead:

Any advise as to how to solve this issue? Thanks in advance. Mark

 

[spikepl]

Im Excel, select the offending column and set its format to text

 

[plog]

...numbers such as 656111 or 65611A

Access works in the decimal numeral system, that's it. If you want 65611A to be a number you must either convert it to a base less than 10, or import it as text.

 

[mkdrep]

Access works in the decimal numeral system, that's it. If you want 65611A to be a number you must either convert it to a base less than 10, or import it as text.

I am trying to import all characters in this field as text whether it is, 65611 or 65611A. However, Access won't let me import the 65611A fields.....

 

[plog]

A fail safe way to do this is to make the first row contain a record that has a non-numeric value. Make the '65611A' record the first row in the spreadsheet and Access will ensure that entire column is imported as text.

 

[Cotty42]

It seems to be a Microsoftism where it doesn't matter what data type you set the column to in Excel or what data type you set to import it to in Access - Access will read the first line of data and determin what data type it should be therefore 656111 will be imported as long integer, irrespective of whether the Excel data type is text or the Access data type is String.

I have never found a way around this so, in effect you need to do what plog suggests. I generally amend the first line of the Excell data by putting a letter at the beginning (or end) of the cell value before performing the import and then removing it from the record in Access once imported. (Bit of a Pain, I know, but I've found it is the quickest way - Unless the Guru's of the forum know better).