How to import numbers from Excel

[Linda]

Right now I am asked to import some numeric data from Excel to Access as text
data. My question is: Some of the fields in the Excel file are formatted as
numbers. Without changing any formats in the Excel file, how would you import
all the data as text fields into Access?

 

[John Nurick]

Hi Linda,

I'd do one of the following:

1) Temporarily link or import the Excel data and use an append query to
move the data to an Access table. To convert the numbers to text I'd use
the CStr() or Format() function in calculated fields in the query.

2) Export the data from Excel to a CSV file, create a table in Access
with the appropriate fields (text fields where needed), and import to
that.

3) Make a copy of the Excel workbook, use VBA code to put an apostrophe
in front of each value in the numeric fields, and then import to Access.
(If some of these numbers are the result of formulas, you'll also need
to use Copy - Paste Special|Values to replace the formulas with literal
values, or the TEXT() function to convert the formula results to text.)

Strictly speaking, adding apostrophes as in (3) doesn't change any
formats in the Excel file. But depending on the cell formats it may
change the way the values are displayed (e.g. displaying numbers
left-aligned instead of right-aligned).