CODEWITHSUNDEEP
ms-accessimporttype-conversion
Trevor D
Trevor D

Reputation: 743

Importing 'numbers stored as text' from Excel to Access in field with 'number' as data type

I know I can just convert to number in Excel, but I will be handing this database off to a few users with no Access experience and limited Excel experience--I want as little data manipulation as necessary. Access use has been reduced to button-clicking and report viewing/printing. Among these buttons is a data refresh macro--the fewer the steps in preparing the data, the lower the chance of screwing something up.

I'm adjusting a .mdb Access table to include information previously excluded from an Excel import. A few of these columns are numbers stored as text in Excel, while the columns in Access have datatype set to "Number". When I import, will Access treat these as a number or a string?

Thanks!

Upvotes: 0

Views: 6100

Answers (1)

Trevor D
Trevor D

Reputation: 743

It does indeed appear to store these values as numbers--it sorts appropriately and I get a value for average.

Update: I just tried to import an Excel file (with numbers stored as text) into a new table, manually changed the data type for this field to Double during the import process, and Access did not import any of the numbers. I deleted all records, saved the table, imported again (this time into the already existing table) and it went through successfully.

Worth noting that you can import numbers stored as text to an existing table, but not into a new table.

Upvotes: 4

Related Questions