Transferspreadsheet losing values

[Bill Sturdevant]

I have a spreadsheet with the following values in columns
A & B of the first 10 rows:

Col A Col B
1 10001:ABC
2 10002:ABC
3 10003:ABC
4 10004:ABC
5 10005:ABC
6 10006:ABC
7 10007:ABC
8 10008:ABC
9 10009
10 10010:ABC

Column B is formatted as Text in Excel

The result of the import is that all of the alphanumeric
values are import, but the purely numeric one is not.
That field in the import table is empty

How can I do this so that every value is imported?

 

[Bill Sturdevant]

FYI, I figured it out, at least to make it work, though I
still cannot explain why excel works this way...

The column in excel had been converted to format "text"
before the alphanumeric values were placed in the column,
but AFTER the numeric values had been placed there. The
numeric values were still considered numbers. To get them
to be considered text, at least one digit in each numeric
value had to be rekeyed and the cell saved. The numeric
value was now considered to be text and the cell was
imported!

If anyone can clarify what both Excel and Access are doing
in this circumstance, I would be grateful!

Bill

 

[Joe Fallon]

When Access imports an Excel file it looks at the first 8 rows of data and
makes a guess about the datatype.
The best way to affect this guess is to ensure that the first row of data
has a "suitable" piece of data in it.

e.g. you expect column B to contain alphanumeric data then in row 1 of data
be sure to include some characters.
ABC123
the rest of the rows could all "look like numbers"
456
789
etc.
But since one of the sample rows had characters in it, the guess for
datatype will be Text, not Number.

Best bet sometimes is to copy paste special values only into a new blank
worksheet and re-arrange rows so sample data falls into the first 8.