Sql – the fastest method for importing from the active sheet in Excel to SQL Server

adoexceljetsql server

What is the fastest way to import data into a SQL Server database directly from the active worksheet in Excel?

I have had great success simply looping through the rows and columns, generating a SQL string, opening an ADODB.Connection and executing the SQL. That solution, however, is too slow for large datasets.

So I am testing the Jet Provider with ADO. It's definitely faster, but it requires that the workbook be saved, and I cannot require the user to save the workbook before uploading from it.

The following code works if the workbook has been saved, but if ActiveWorkbook has never been saved ActiveWorkbook.Path returns a null string and the code fails.

Sub UploadViaJet()

    Dim objADO As New ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long

    Set objADO = New ADODB.Connection

    objADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & ActiveWorkbook.Path & _
        "\" & ActiveWorkbook.Name & ";" & _
        "Extended Properties=Excel 8.0"

    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<UID>;PWD=<PWD>].test_table " & _
        "FROM [" & ActiveSheet.Name & "$]"

    objADO.Execute strSQL, lngRecsAff, adExecuteNoRecords

End Sub

Is there a simpler and faster way to do this?

Best Answer

You can create a datasource (using a linked server or OPENROWSET) and just do a SELECT on SQL Server side:

SELECT  *
INTO    mytable
FROM    OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\myfile.xls', 'SELECT * FROM [Sheet$]')

The path with the Excel workbook should be accessible by the SQL Server.

Here is a KB article on how to do this.

Related Topic