What is the fastest method for importing from the active sheet in Excel to SQL Server?

Question

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?

Answer 1

Generally I import large Excel files using SSIS.

Answer 2

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.

Answer 3

Have you tried just putting an ActiveWorkBook.SaveAs("some temp filename") at the top, and importing from that? I do something similar with a macro to FTP the contents of the ActiveSheet to a remote server and it works.

Ron

Answer 4

Solution using Import Excel File

Since you have a restriction on not forcing the user to save the file, and most proposed solutions require that the file be saved, I would use VBA to

• create a workbook object in memory
• copy paste from active worksheet to workbook
• SAVEAS workbook to a temp file name (TempExcelFile.xls) and path
• use Quassnoi's method above to import into SQL Server
• overwrite TempExcelFile.xls with zeroes
• delete TempExcelFile.xls

This way the user will not be forced to save the worksheet, and they will be prompted to save it just like they would during the natural course of their work.

To facilitate multiple active worksheets being simultaneously imported, I would use either a timestamp or a GUID to create the file names.

---

Solution using ADO Queries

Have your users create their workbooks from a template that has VBA macros built in Make sure you protect the workbook to prevent unauthorized access to the macros/code behind.

Execute a macro that will loop through the rows and columns, read the cell values, build a SQL query and execute it - use stored procedures with parameters to prevent injection attacks.