Import Errors

[Guest]

I have an Excel spreadsheet where the relevant column is formatted as text.
Entries in that column may have numbers alone or numbers and letters. I have
an Access database where I use an Access macro to make a table and import the
excel information. The table field into which the excel text is imported has
a text format. However, when I run the macro, I get errors identified as Type
conversion errors. Where the excel data contained numbers and letters, Access
thinks it should have been numbers even though the field is formatted as
text. Access leaves the field blank. Any help?

The macro uses the Transfer Spreadsheet Action and Import Transfer type.

 

[Ken Snell \(MVP\)]

A commonly asked question....

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.

 

[aaron.kempf]

this ins't an access problem; the problem is that excel is inherently
CRAP and it doesnt have real datatypes

excel is a disease; any data pushed into excel cannot reliably be
imported back into the database world

excel is a dead end street; a disease.

keep your data in a database and screw excel in the mouth