Force Data Types When Importing Excel

[Guest]

In Access 2003, when I'm trying to import a spreadsheet from Excel, Access
tries to guess which data types to use based on the values in the cells. For
instance, if it sees a bunch of numerical values, it creates the new field as
Double.

However, there are times when I want to import this data as text, and it
won't let me change the data type (the dropdown of different data types is
grayed out). Is there any way I can force Access to import this data as text?

 

[Guest]

Convert the spreadsheet to a CSV or TXT file, then use the Import Text Wizard
to import this file and identify each imported field's data type. If this
import is going to be done on a recurring basis, then save these settings as
an Import Specification.

If the table to be imported into already exists, then the imported field
data types will match the predefined data types in the table -- unless they
can't be converted, in which case an import errors table will be created
listing the errant row number and why it couldn't be imported.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[John Nurick]

An alternative approach is to make Excel treat the numbers as text. Do
this by prefixing each with an apostrophe, e.g.
'1234567

The apostrophes aren't displayed in worksheet or imported into Access,
but do ensure that the column is imported as a text field. Here are a
little Excel macro and procedure that make it easy to add them:

Sub AddApostrophesNumericToSelection()
'adds apostrophes to numeric values in selection
Dim C As Excel.Range
For Each C In
Application.Selection.SpecialCells(xlCellTypeConstants).Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub AddApostrophesAllToColumn( _
ByVal TheColumn As Long _
)
'adds apostrophes to all used cells in a column
Dim C As Excel.Range
With ActiveWorkbook.ActiveSheet
For Each C In Intersect(.Columns(TheColumn), _
.UsedRange).SpecialCells(xlCellTypeConstants).Cells
C.Formula = "'" & C.Formula
Next
End With
End Sub