Jim P.
asked on
Handling Zip+4 in VBA import
I'm trying to import data into my DB from an Excel SS or a CSV file. Here's the code I'm using:
The problem is that I have a zip column that a few of the records have a few zip+4 like "44081-1234" which when viewing the linked table comes up as #Num and the properties say it is a long integer in the linked table.
The data set is small enough that I can just copy and paste to the staging table. But does anyone have any suggestions to get around this if I want to automate the import process?
FilePath = "C:\Users\residents.csv"
'DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel7, "TblNm_SS", FilePath, True
DoCmd.TransferText acLinkDelim, , "TblNm_SS", FilePath, True
Set InRS = DB.OpenRecordset("TblNm_SS")
Set OutRS = DB.OpenRecordset("PermTbl")
If InRS.EOF = False Then
InRS.MoveFirst
Do Until InRS.EOF = True
I = 0
With OutRS 'the input is an empty string write it
.AddNew
For I = 0 To InRS.Fields.Count - 1
.Fields(I).Value = CStr(Nz(InRS.Fields(I).Value, ""))
Next I
.Update
End With
InRS.MoveNext
Loop
End If
The problem is that I have a zip column that a few of the records have a few zip+4 like "44081-1234" which when viewing the linked table comes up as #Num and the properties say it is a long integer in the linked table.
The data set is small enough that I can just copy and paste to the staging table. But does anyone have any suggestions to get around this if I want to automate the import process?
ASKER
The destination table in Access is already setup as a text field. The issue is that the VBA wants to handle the attached file as a long integer and or just an integer so I'm getting a Numeric Overflow error.
Few options:
.Fields(I).Value = CStr(Nz(InRS.Fields(I).Val ue, ""))
Determine which field number it is and when you get to it, simply use REPLACE.
Example, if the Zip Code field is the 6th field, then:
If i = 6 Then
.Fields(I).Value = Replace(CStr(Nz(InRS.Field s(I).Value , "")), "-", "")
Else
.Fields(I).Value = CStr(Nz(InRS.Fields(I).Val ue, ""))
End If
Or open the source data and do a replace on that field in excel.
.Fields(I).Value = CStr(Nz(InRS.Fields(I).Val
Determine which field number it is and when you get to it, simply use REPLACE.
Example, if the Zip Code field is the 6th field, then:
If i = 6 Then
.Fields(I).Value = Replace(CStr(Nz(InRS.Field
Else
.Fields(I).Value = CStr(Nz(InRS.Fields(I).Val
End If
Or open the source data and do a replace on that field in excel.
ASKER
leew,
Still doesn't work. My error is "Numeric field overflow." because it is still trying to handle it as in integer.
Still doesn't work. My error is "Numeric field overflow." because it is still trying to handle it as in integer.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Alternatively you could adjust the table to not have an issue with the - by changing it to a text field.
-SA