Importing text Field from Excel - Numeric characters appear as numbers in scientific format - Access

[Leonard Priestley]

I have imported several fields from a 1000 record spreadsheet into an Access
table. All of the fields but one have been imported perfectly. The
problem is with a column of serial numbers. They are formatted as text in
Excel, and many of them appear correctly when imported, irrespective of
whether they are all numeric characters or a mix of numeric and alphabetic.
However, there are a large number of moderately long serial numbers which
appear in the Access table as if they were in scientific notation. So a
serial number which should appear as, for instance, 3611325 appears as
3.61133e + 006. The field in the Access table is definitely a text field,
and I extended its length from 50 characters to 100 characters by way of an
experiment, but it had no effect. I can't figure out whether my problem is
in Excel or in Access. Any suggestion would be very welcome because I can't
face wading through 1000 records, fixing them manually.

Leonard Priestley

 

[Vincent Johns]

I have imported several fields from a 1000 record spreadsheet into an Access
table. All of the fields but one have been imported perfectly. The
problem is with a column of serial numbers.

What do you want them to be in Access? If numbers, you might convert
them to numbers in Excel (insert a blank column, copy the values to it
as numbers using =VALUE() ) and import the numbers.

They are formatted as text in
Excel, and many of them appear correctly when imported, irrespective of
whether they are all numeric characters or a mix of numeric and alphabetic.

"mix of ... alphabetic" doesn't sound to me like "serial numbers". If
you want to import them as text, then you can make sure they're all text
by converting them using something like =TEXT(B3,"0") , and Access
should leave them in text form (no scientific notation).

However, there are a large number of moderately long serial numbers which
appear in the Access table as if they were in scientific notation. So a
serial number which should appear as, for instance, 3611325 appears as
3.61133e + 006. The field in the Access table is definitely a text field,
and I extended its length from 50 characters to 100 characters by way of an
experiment, but it had no effect.

I couldn't reproduce what you found; the number (3611325) Access
converted to Double (a floating-point number, not an integer) but made
no conversion to scientific notation, so I'm not sure how you did that.
The string "3611325" Access left as a string (no change).

I can't figure out whether my problem is
in Excel or in Access. Any suggestion would be very welcome because I can't
face wading through 1000 records, fixing them manually.

Leonard Priestley

In summary, I suggest you fix them in Excel first, to be what you want
(either text or numbers), then import them that way into Access.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.