Importing excel info to Access

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

G

I have a column called Account Numbers in an excel spreadsheet.
I have tried to import this into an access database that has an equivalent
field.
However only certain numbers in the column will import successfully and
others won't.
Seems like the ones with over 9 digits do not import successfully.
The field size setting for this column in access is set to DOUBLE. I tried
LONG INTEGER but still no success.
 
Double should be able to handle the size of your numbers. I would suggest
checking you excel file to make sure the information being imported is
actually a number. If excel is storing the data as text, then Access will
be trying to import text as a number which it can't. Set your data type to
text and see what happens. Access is good at converting numbers to text but
gets confused the other way around.

Kelvin
 
Yes, you would have problem with any account numbers > 2 ^
31, approx. 2,000,000,000. I wouldn'r recommend Double
since Double is not exact represtation of numbers.

Since you are not doing maths with the AccountNo, you
should be storing it as Text rather than Long.

Create a Column in youe Excel spreadsheet and use the
spreadsheet function TEXT() to conver the numeric
AccountNo to Text AccountNo. When you import the
spreadsheet, skip the original Column and import the TEXT
Column. Access will import the Column As Text containing
obly digit characters.

Alternatively, you can set up the structure of the
desination Table first (i.e. Filed types are defined) and
then use an Append Query to append the Excel rows to the
Table.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top