Access Type conversion failure importing from Excel

Question

I'm trying to import an Excel to Access DB, and some customer numbers cannot be imported due to Type Conversion Failure. It is 12 entries out of 66 thousand. Normally the customer number is a number, but these 12 are strings like ABCDEFT001. I tried setting the field of the table to Long Text or Short text, they are still not imported (only to ImportError table). Do you know what else I can try? Thank you very much in advance! P.S. I'm trying to import using DoCmd.TransferSpreadsheet

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Inv level", "Path/to/file.xlsb", True, "Sheetname!"

DoCmd.TransferSpreadsheet tries to be helpful by assuming the datatype for you. What you may want to do is rather than use acImport, use acLink to link to the Excel Sheet. Then have a query that casts all the fields to the right datatype and selects the data into a Access table. — ArcherBird

ArcherBird ArcherBird · Accepted Answer · 2020-04-06T18:46:54

This strategy links to the excel file, instead of directly importing it, and then selects data out of it and casts any fields that need it to the correct datatype.

Sub ImportFromExcel()
    Dim pathToFile As String
    Dim targetTableName As String
    Dim sql As String

    pathToFile = "C:\Path\To\File.xlsx"
    targetTableName = "ImportResults"

    '//create the link
    DoCmd.TransferSpreadsheet acLink, _
                              acSpreadsheetTypeExcel12, _
                              targetTableName, _
                              pathToFile, _
                              True '//This part only if the excel file has field headers


    '//import
    sql = "SELECT Field1Name, CStr(CustNumber) AS CustNumber, Field3Name INTO NewImportTable FROM " & targetTableName
    CurrentDb.Execute sql

    '//remove the link
    DoCmd.DeleteObject acTable, targetTableName
End Sub

***Two pitfalls of this code to be aware of:

1) You should delete any table with the name "NewImportTable" before running this code, or you'll get a "Table Already Exists" error.

2) If any errors happen in this Sub before you remove the link, you'll have an issue the next time it runs, as it will create a link called "ImportResults1" since "ImportResults" would still exist. The really scary thing is that no errors would be thrown here. It would create "ImportResults1" and then run your sql on "ImportResults"!!

Access Type conversion failure importing from Excel

1 Answers