J
Jason Lepack
I'm systematically importing data from bunch of spreadsheets that are
formatted as General the whole way through. Each file is imported into
the table, handled, appended to the master table, and then moves on to
the next file.
My problem is that in one row I have this data:
1234567
1234568
1234ABC
The field that should be 1234ABC will not be imported and will be
placed in the ImportErrors table that Access creates.
All the fields in the table that I am importing to are "Text" with a
length of 255.
I figure that Access isn't looking at the table, it looks at the
spreadsheet and says "yup this is a number column" and when it hits
1234ABC it says "nope, that's not a number in this here number column."
Is there another way I can work around this other than setting
"hasHeader" to false and letting it determine that the fields are all
text based on the header.
PS - I had problems with Access not importing the data out of order,
that's why I check to see that the first field is Parent and import
again otherwise.
Here's my code:
|--------------- Clip of prcedure code here
Do While Len(strFile) > 0
loaded = 0
Do While loaded = 0
loaded = loadFile(FILEDIR & strFile)
Loop
If loaded = -1 Then
' record the exception
Else
' handle the data
End If
strFile = Dir
Loop
|----------------- Clip of code here
Private Function loadFile(fName As String) As Integer
On Error GoTo lfErr
Dim db As Database, rs As Recordset
Set db = CurrentDb
db.Execute "DELETE * from tbl_import_temp", dbFailOnError
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tbl_import_temp", fName, True, "B:G"
Set rs = db.OpenRecordset("tbl_import_temp")
rs.MoveFirst
If rs.Fields("Level") = "PARENT" Then
loadFile = 1
Else
loadFile = 0
End If
lfGoodbye:
Set db = Nothing
Set rs = Nothing
Exit Function
lfErr:
MsgBox "DESCRIPTION: " & Err.description & vbCrLf & "ERROR NUMBER:
" & Err.Number & vbCrLf & vbCrLf & "File was not Loaded", vbOKOnly,
"TRAPPED ERROR"
loadFile = -1
Resume lfGoodbye
Resume Next
End Function
formatted as General the whole way through. Each file is imported into
the table, handled, appended to the master table, and then moves on to
the next file.
My problem is that in one row I have this data:
1234567
1234568
1234ABC
The field that should be 1234ABC will not be imported and will be
placed in the ImportErrors table that Access creates.
All the fields in the table that I am importing to are "Text" with a
length of 255.
I figure that Access isn't looking at the table, it looks at the
spreadsheet and says "yup this is a number column" and when it hits
1234ABC it says "nope, that's not a number in this here number column."
Is there another way I can work around this other than setting
"hasHeader" to false and letting it determine that the fields are all
text based on the header.
PS - I had problems with Access not importing the data out of order,
that's why I check to see that the first field is Parent and import
again otherwise.
Here's my code:
|--------------- Clip of prcedure code here
Do While Len(strFile) > 0
loaded = 0
Do While loaded = 0
loaded = loadFile(FILEDIR & strFile)
Loop
If loaded = -1 Then
' record the exception
Else
' handle the data
End If
strFile = Dir
Loop
|----------------- Clip of code here
Private Function loadFile(fName As String) As Integer
On Error GoTo lfErr
Dim db As Database, rs As Recordset
Set db = CurrentDb
db.Execute "DELETE * from tbl_import_temp", dbFailOnError
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tbl_import_temp", fName, True, "B:G"
Set rs = db.OpenRecordset("tbl_import_temp")
rs.MoveFirst
If rs.Fields("Level") = "PARENT" Then
loadFile = 1
Else
loadFile = 0
End If
lfGoodbye:
Set db = Nothing
Set rs = Nothing
Exit Function
lfErr:
MsgBox "DESCRIPTION: " & Err.description & vbCrLf & "ERROR NUMBER:
" & Err.Number & vbCrLf & vbCrLf & "File was not Loaded", vbOKOnly,
"TRAPPED ERROR"
loadFile = -1
Resume lfGoodbye
Resume Next
End Function