Type conversion failure during the import of excel to access database

[fluffyvampirekitten]

I have been trying to import Excel spreadsheets into MS Access 2013.
Then it keep producing "Type Conversion Failure" errors for a field "DOB" - date type.

I tried to change the data type of my access table for column "DOB" but there's no luck.
How can i import the table successfully without having "import errors table".

Any ideas? Thank yo uso much





The code below is the one that I used to import data into access

Private Sub cmd_YesUpdateSAP_Click()
Dim fDialog    As Object
Dim SourcePath As String
Dim Success As Boolean
Dim DelReqUpdateSAPSQL As String
Dim LoadReqUpdateSAPSQL As String

On Error GoTo ErrorHandler
    
    Set fDialog = Application.FileDialog(3)
    Dim varFile As Variant
    Success = False
    With fDialog
       .AllowMultiSelect = False
       .Title = "Select excel file to import the data :"
       .InitialFileName = "c:\"
        If .Show = True Then
           For Each varFile In .SelectedItems
             SourcePath = varFile
           Next
           Success = True
        Else
           Success = False
        End If
    End With
    
    If Success Then
    
        DelReqUpdateSAPSQL = "Delete * From UPDATE_SAP"
        DoCmd.RunSQL DelReqUpdateSAPSQL
        DoCmd.TransferSpreadsheet acImport, 8, "UPDATE_SAP", SourcePath, True, ""
                                         
    End If
    
    
ErrorHandlerExit:
    Exit Sub
    
ErrorHandler:
   MsgBox "Please import the correct file!"
    Me.Refresh
Resume ErrorHandlerExit
End Sub

[JoeM]

I am guessing this is related to your other question.
Are you importing into an existing Access table, or is this process creating a new one?
If an existing data table, what is the Data Type of your DOB field in your Access table?
If it is Date, every entry in your Excel file must be in a valid Date Format (and not entered as Text).

[NTC]

there are 2 things with this type of error:
a. the field type of the excel and access must both be the same ; in your case - date fields
b. excel tolerates non conforming data - i.e. text in a number/date field - this means you must pre-emptively review the excel sheet before attempting the import and remove/correct the offending data

[fluffyvampirekitten]

I am guessing this is related to your other question.
Are you importing into an existing Access table, or is this process creating a new one?
If an existing data table, what is the Data Type of your DOB field in your Access table?
If it is Date, every entry in your Excel file must be in a valid Date Format (and not entered as Text).

I'm importing into an existing Access table.
Initially , the Data Type in my DOB field is Date/Time - Short Date .
And, In my excel file , the date is "dd/mm/yyyy" however there's some rows that are (blank) and with " - " . So , I guess these are the cause of the problem.

[fluffyvampirekitten]

there are 2 things with this type of error:
a. the field type of the excel and access must both be the same ; in your case - date fields
b. excel tolerates non conforming data - i.e. text in a number/date field - this means you must pre-emptively review the excel sheet before attempting the import and remove/correct the offending data

There's some rows that are (blank) and with " - " . So as you said , I guess these are the cause of the problem.

Is there any way that I can filter out the (blank) and " -" rows before it was successfully loaded in my table? Hence , I only want those rows without (blanks) & "-" to appear in my Access table.

[JoeM]

You should be able to filter them out in Excel before importing, using Excel's built in Filters.
Otherwise, you may want to first import the file to a temporary table in Access which allows anything in this field (Text), then write an Update Query which only selects the records with dates and writes these records to your final destination table in Access.