Assigning Column Type when importing Data from Excel and Html

[Guest]

I have had this problem with two seperate data types. When I import a
spreadsheet into access I have a column which has test numbers. Most of the
numbers are numeric but some of the test numbers are not. Some of the tets
numbers start with a letter. When I try to import the test numbers if the
first test number is numeric it treats all the data in the column as numeric
even though the column the data is being loaded into is text and the column
in the spreadsheet is formated as text. When the test numbers starting with
a letter are imported I get an import error, type conversion error, and the
field is not loaded. I do not have control of the import file so I can't
really modify it.

Any brillant ideas? - BCP

 

[Guest]

We also run into this issue all of the time (frustrating!) - - the only way
we are aware to get around this is to either make sure that the first row of
data is a text value (with a letter in it) - by sorting the data before
importing - - or we add a 'dummy' row of values and type a letter for columns
that should be text and type a number if all values in the column are
numeric. Then we delete the row in Access after importing.

 

[John Nurick]

For an alternative (and more under-the-bonnet) approach, see
http://www.dicks-blog.com/excel/2004/06/external_data_m.html