How can your force a field with mostly numeric data to import as text

[SBendBuckeye]

My current project involves importing multiple Excel files into an Access database. There are column headers in Row 1. Most of the data for one column is numeric, but alphanumeric data is also valid. How can I force Access to import a given column as text instead of numeric data?

Thanks for any help you can give me!

Have a great day!

 

[PBAPaul]

SBendBuckeye

If you are importing using VBA, then you can use the function Str and IsNumeric

Sub Test()
Dim A As Variant

A = "Hello" or 123
If IsNumeric(A) = True Then
A = Str(A)
End If
MsgBox A
End Sub

if A is "Hello" then it is not converted as it is already a string.
If A is 123 then it is converted into a string.

See Excel VBA Help examples of Str to see what happens

Paul

 

[SBendBuckeye]

Thanks PBAPaul! The problem is that the import is into a new table so Access is creating a TableDef based on the data coming in. I may have several hundred numeric values and only a handful of non-numeric data way down spreadsheet.

Do I have to spin through every cell to verify this situation?

Thanks again!

 

[BrainB]

It is a pain. Instead of importing "blind' try setting up an empty table to accept the data with fields formatted as required and inporting into that. Next time remove all of the records before re-importing. Regards
BrianB
** Let us know if you get something that works !
================================

 

[mmandk9]

SBendBuckeye,

I had a similar issue in Access97. However, my issue was getting information from text files (.csv to be exact). In the transfertext method you can indicate a Specification Name - basically a set of rules on how to import the data. This way, I was able to indicate that certain columns should be imported as text when they contained purely numeric codes. (How to do this is in the tranfertext help pages). There are 2 issues: first, I still lost leading zeroes (not sure why, but I came up with a work-around for that)...and second, there is no equivalent in the transferspreadsheet command (Access97), but perhaps you could save each spreadsheet temporarily as a text file and import it according to rules you create...? Good luck. Let us know

MM

 

[SBendBuckeye]

Here is what I did. If a column is flagged for checking then if it is entirely numeric the only nonnumeric row should be row1 which is the column header. I used specialcells and counted the text areas and then used it again to count the numeric rows.

I used the areas count for the strings because rows only returns the rows for the first area (if more than 1) which could be misleading. It seems to be working just fine.

Then after the import I checked row 1 for the specified fields by spinning through the ColumnsAffected array and stripped off the string I had prepended to force it to text. Merely prepending an Excel string indicator did not seem to work.

Sorry for the ugly formatting but I left it alone so it would hopefully paste properly into a module if someone wanted to use it. If it gives you trouble, post an email address and I'll try to send it to you.

Thanks for the help and suggestions. Hopefully this will help save someone the grief I encountered.

************* Begin Code *******************8
Public Sub Sub_ValidateImportColumnTypes(pxlWorkSheet As Worksheet, _
ByRef ravarColumnsAffected As Variant)
Dim xlWorkSheet As Worksheet
Dim rng As Range
Dim lngCol As Long
Dim lngRow As Long
Dim lngText As Long
Dim lngSectionCount As Long
Dim lngNumeric As Long
Dim lngBlanks As Long
Dim blnNonNumeric As Boolean
Dim strText As String

On Error Resume Next 'No cells were found. - Raises 1004 error

strText = "ForceToString"
'Activate requested Worksheet
pxlWorkSheet.Activate

'Just returns specialcells(xlLast)
lngRow = Func_GetLastRow(pxlWorkSheet)

'Spin through all of the columns in the worksheet
For lngCol = 1 To pxlWorkSheet.Columns.Count

'Does the current column have anything in row 1
If Len(pxlWorkSheet.Cells(1, lngCol)) > 0 Then

'debug MsgBox lngCol & " ~" & pxlWorkSheet.Cells(1, lngCol) & "~" 'debug
'Ensure no leading spaces in column names
pxlWorkSheet.Cells(1, lngCol) = Trim$(pxlWorkSheet.Cells(1, lngCol))

pxlWorkSheet.Range(Cells(1, lngCol), Cells(lngRow, lngCol)).Activate

With pxlWorkSheet.Range(Cells(1, lngCol), Cells(lngRow, lngCol))

'NOTE: Remember that if multiple sections are returned by the range
' assignment below then the row counts only refer to secion 1

'Count the Text cells
Set rng = .SpecialCells(xlCellTypeConstants, xlTextValues)
If Err.Number = 0 Then
lngText = rng.Rows.Count
lngSectionCount = rng.Areas.Count
'If more than 1 text section or more than 1 text row then text
If lngText > 1 Or lngSectionCount > 1 Then
blnNonNumeric = True
Else
blnNonNumeric = False
End If
'debug MsgBox intX & " " & rng.Address & " " & rng.Areas.Count 'debug
Else
Err.Clear
lngText = 0
End If

'Count the Numeric cells
Set rng = .SpecialCells(xlCellTypeConstants, xlNumbers)
If Err.Number = 0 Then
lngNumeric = rng.Rows.Count
'debug MsgBox intX & " " & rng.Address & " " & rng.Areas.Count 'debug
Else
Err.Clear
lngNumeric = 0
End If

'Count the Blank cells
' Set rng = .SpecialCells(xlCellTypeBlanks)
' If Err.Number = 0 Then
' lngBlanks = rng.Rows.Count
'debug MsgBox intX & " " & rng.Address & " " & rng.Areas.Count 'debug
' Else
' Err.Clear
' lngBlanks = 0
' End If

'if mixed data found in the column then ensure row 2 is text to force text import
If blnNonNumeric And lngNumeric > 0 Then
If IsNumeric(.Cells(2, lngCol)) Then

'Force column to be treated as text by prepending a text string to it
pxlWorkSheet.Cells(2, lngCol).Activate
pxlWorkSheet.Cells(2, lngCol) = strText & pxlWorkSheet.Cells(2, lngCol)

'Save this column header in the ColumnsAffected array
If IsEmpty(ravarColumnsAffected) Then
ReDim ravarColumnsAffected(1, 0)
Else
ReDim Preserve ravarColumnsAffected(1, UBound(ravarColumnsAffected, 2) + 1)
End If
'NOTE: Do not change the following string as it is used elsewhere in the code
ravarColumnsAffected(0, UBound(ravarColumnsAffected, 2)) = "Affected Column"
ravarColumnsAffected(1, UBound(ravarColumnsAffected, 2)) = _
pxlWorkSheet.Cells(1, lngCol).Value

End If
End If

End With
End If
Next lngCol

End Sub 'Sub_ValidateImportColumnTypes

***************** End Code *************************

 

[glenntb]

I had a similar issue here. Access seems to determine the format of each column based on whatever the "first" value is. (The "first" value being defined as the first non-blank cell found in each column ignoring the column headings.)

For instance if your Excel data looks like:
Value1 Value2 Value3
1 2
2 X 3

then Access will format your new table as:
Integer, Integer, Integer
and the "X" from above won't be imported.

I solved my problem by forcing the "first" value to a text string by
a) Appending an "A" onto the end of the "first" real value in a column that I needed to be forced as Text, or
b) Replacing the first empty field with an "A" in a column that I needed to be forced as Text

Like this:
Value1 Value2 Value3
1 2A A
2 X 3
This forces Access to format your new table as:
Integer, Text, Text
and everything in these columns will be imported.

Then after the import was successful, I just edit the first data record in my Access table and restore the data back to it's original correct-ness (if that's a word).

Hope this helps!
:

 

[SBendBuckeye]

Thanks, Glenn. Your example is much simpler. Mine is complicated by the fact that this is a generic function and must be data driven so it never knows before it does the processing whether or not it is a numeric column.

Have a great day!