Text read as number during import resulted in conversion error?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I imported an excel spreadsheet I received data conversion errors when a
column formatted as text that contained numbers (i.e. 13440 )went from a pure
number entry to a number with letter entry (i.e. 13440A). I modified the
field in the table to also be text format and still received errors on the
import. I went back and resorted the excel table so the first entry that the
Access import saw was a number text combination (i.e. 13440A) and every line
imported without conversion errors. Access seemed to read the text formatted
number as a pure number establishing the field as a number field during the
import when it was in the first line and then read any number/letter mixes as
text and as a result data conversion errors. Other than my resorting work
around does anybody have a cure?
 
I've seen the same behavior and have gone so far as to create a bogus first
record in the spreadsheet with all the fields filled in with the proper type
of data; import the data; and then delete that first records based on a Where
clause with something in the record that is unique.

I've also had good luck with linking to the spreadsheet then using an append
query to move the records to a table.
 
This page
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
explains in detail what's going on. See also
http://support.microsoft.com/?id=257819

Another approach is to prefix the mixed data values in Excel with
apostrophes. These don't display in the worksheet, but they force Excel
to treat the values as text, which imports happily to Access, using
their apostrophes en route. If you search this group (e.g. at
http://groups.google.com) for my name and the word "apostrophes" you
should find some little VBA procedures for adding and removing them.
 
Back
Top