9

Does anyone know the fastest way to get data from and Excel table (VBA Array) to a table on SQL 2008 without using an external utility (i.e. bcp)? Keep in mind my datasets are usually 6500-15000 rows, and about 150-250 columns; and I end up transferring about 20-150 of them during an automated VBA batch script.

I have tried several methods for getting large amounts of data from an Excel table (VBA) to SQL 2008. I have listed those below:

Method 1. Pass table into VBA Array and send to stored procedure (ADO) -- Sending to SQL is SLOW

Method 2. Create disconnected RecordSet load it, then sync. -- Sending to SQL VERY SLOW

Method 3. Put table into VBA array, loop though the array and concatenate(using delimiters) then send to stored procedure. -- Sending to SQL SLOW, but faster than Method 1 or 2.

Method 4. Put table into VBA array, loop though the array and concatenate(using delimiters) then place each row with ADO recordset .addnew command. --Sending to SQL very FAST (about 20 times faster than methods 1-3), but now I will need to split that data using a separate procedure, which will add significant wait time.

Method 5. Put table in VBA array, serialize into XML, send to stored procedure as VARCHAR and specify XML in stored procedure. --Sending to SQL INCREDIBLY SLOW (about 100 times slower than methods 1 or 2)

Anything I am missing?

Community
  • 1
  • 1
cshenderson
  • 103
  • 1
  • 1
  • 9

6 Answers6

2

There is no single fastest way, as it's dependent on a number of factors. Make sure the indexes in SQL are configured and optimized. Lots of indexes will kill insert/update performance since each insert will need to update the index. Make sure you only make one connection to the database, and do not open/close it during the operation. Run the update when the server is under minimal load. The only other method you haven't tried is to use a ADO Command object, and issue a direct INSERT statement. When using the 'AddNew' Method of the recordset object, be sure to issue only one 'UpdateBatch' Command at the end of the inserts. Short of that, the VBA can only run as fast as the SQL server accepting the inputs.

EDIT: Seems like you've tried everything. There is also what is known as 'Bulk-Logged' recovery mode in SQL Server, that reduces the overhead of writting so much to the transaction log. Might be something worth looking into. It can be troublesome since it requires fiddling with the database recovery model a bit, but it could be useful for you.

Fink
  • 3,356
  • 19
  • 26
  • -Index are not an issue because I am loading into a temp table - but thanks, I had forgotten about that. Definately using only one connection. As for ADO command object, I have tried that but found it to be no different in speed than passing to a sp. And yes, I issued only one ADO update batch after looping through the add new. The .add new method was and still is by far the fastest, but only when used in conjunction with the concat - which would have to be parsed later. – cshenderson May 22 '12 at 21:42
  • I'll look into bulk logged recovery mode. – cshenderson May 24 '12 at 14:35
2

The following code will transfer the thousands of data in just few seconds(2-3 sec).

Dim sheet As Worksheet
    Set sheet = ThisWorkbook.Sheets("DataSheet")        

    Dim Con As Object
    Dim cmd As Object
    Dim ServerName As String
    Dim level As Long
    Dim arr As Variant
    Dim row As Long
    Dim rowCount As Long

    Set Con = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Command")

    ServerName = "192.164.1.11" 

    'Creating a connection
    Con.ConnectionString = "Provider=SQLOLEDB;" & _
                                    "Data Source=" & ServerName & ";" & _
                                    "Initial Catalog=Adventure;" & _
                                    "UID=sa; PWD=123;"

    'Setting provider Name
     Con.Provider = "Microsoft.JET.OLEDB.12.0"

    'Opening connection
     Con.Open                

    cmd.CommandType = 1             ' adCmdText

    Dim Rst As Object
    Set Rst = CreateObject("ADODB.Recordset")
    Table = "EmployeeDetails" 'This should be same as the database table name.
    With Rst
        Set .ActiveConnection = Con
        .Source = "SELECT * FROM " & Table
        .CursorLocation = 3         ' adUseClient
        .LockType = 4               ' adLockBatchOptimistic
        .CursorType = 0             ' adOpenForwardOnly
        .Open

        Dim tableFields(200) As Integer
        Dim rangeFields(200) As Integer

        Dim exportFieldsCount As Integer
        exportFieldsCount = 0

        Dim col As Integer
        Dim index As Integer
        index = 1

        For col = 1 To .Fields.Count
            exportFieldsCount = exportFieldsCount + 1
            tableFields(exportFieldsCount) = col
            rangeFields(exportFieldsCount) = index
            index = index + 1
        Next

        If exportFieldsCount = 0 Then
            ExportRangeToSQL = 1
            GoTo ConnectionEnd
        End If            

        endRow = ThisWorkbook.Sheets("DataSheet").Range("A65536").End(xlUp).row 'LastRow with the data.
        arr = ThisWorkbook.Sheets("DataSheet").Range("A1:CE" & endRow).Value 'This range selection column count should be same as database table column count.

        rowCount = UBound(arr, 1)            

        Dim val As Variant

        For row = 1 To rowCount
            .AddNew
            For col = 1 To exportFieldsCount
                val = arr(row, rangeFields(col))
                    .Fields(tableFields(col - 1)) = val
            Next
        Next

        .UpdateBatch
    End With

    flag = True

    'Closing RecordSet.
     If Rst.State = 1 Then
       Rst.Close
    End If

   'Closing Connection Object.
    If Con.State = 1 Then
      Con.Close
    End If

'Setting empty for the RecordSet & Connection Objects
Set Rst = Nothing
Set Con = Nothing
End Sub
SaiKiran Mandhala
  • 365
  • 2
  • 14
  • 37
0

By far the fastest way to do this is via T-SQL's BULK INSERT.

There are a few caveats.

  • You will likely need to export your data to a csv first (you may be able to import directly from Excel; my experience is in going from Access .mdbs to SQL Server which requires the interim step to csv).
  • The SQL Server machine needs to have access to that csv (when you run the BULK INSERT command and specify a filename, remember that the filename will be resolved on the machine where SQL Server is running).
  • You may need to tweak the default FIELDTERMINATOR and ROWTERMINATOR values to match your CSV.

It took some trial and error for me to get this set up initially, but the performance increase was phenomenal compared to every other technique I had tried.

mwolfe02
  • 23,787
  • 9
  • 91
  • 161
  • Thank you, but BCP is not an option. I deal with thousands of formats on the fly and bcp has given me nothing but trouble on just enough of them to cause disaster each and every time. I need something that I can control with error feedback during the batch loop; and that eliminates most bulk programs... especially bcp. – cshenderson May 22 '12 at 21:50
  • I didn't realize BCP == `BULK INSERT`. That said, I had similar issues myself working with BCP initially. I don't know the specifics of your situation, but I solved the problems I had using custom row and field terminators and "massaging" the data when I exported it to csv. I tried variations of most of what you listed and the performance never came close to the Bulk Insert. I agree that Bulk Insert is "finicky" (to say the least) and error feedback during the batch loop is next to impossible (without resorting to some sort of kludge), but I think it's worth another look. Best of luck! – mwolfe02 May 23 '12 at 02:45
0

works pretty fine, on the other hand to improve speed we may still modify the query:

Instead: Source = "SELECT * FROM " & Table

We can use: Source = "SELECT TOP 1 * FROM " & Table

Here is we only need column names. So no need to maka a query for entire table, which is extending the process as long as new data imported.

Asieh hojatoleslami
  • 3,240
  • 7
  • 31
  • 45
FKSP
  • 1
  • 1
0

As far as I remember, you can create a linked server to the Excel file (as long as the server can find the path; it's best to put the file on the server's local disk) and then use SQL to retrieve data from it.

darlove
  • 317
  • 2
  • 10
0

Having just tried a few methods, I came back to a relatively simple but speedy one. It's fast because it makes the SQL server do all the work, including an efficient execution plan.

I just build a long string containing a script of INSERT statements.

    Public Sub Upload()
        Const Tbl As String = "YourTbl"
        Dim InsertQuery As String, xlRow As Long, xlCol As Integer
        Dim DBconnection As New ADODB.Connection

        DBconnection.Open "Provider=SQLOLEDB.1;Password=MyPassword" & _
            ";Persist Security Info=false;User ID=MyUserID" & _
            ";Initial Catalog=MyDB;Data Source=MyServer"

        InsertQuery = ""
        xlRow = 2
        While Cells(xlRow, 1) <> ""
            InsertQuery = InsertQuery & "INSERT INTO " & Tbl & " VALUES('"

            For xlCol = 1 To 6 'Must match the table structure
                InsertQuery = InsertQuery & Replace(Cells(xlRow, xlCol), "'", "''") & "', '"  'Includes mitigation for apostrophes in the data
            Next xlCol
            InsertQuery = InsertQuery & Format(Now(), "M/D/YYYY") & "')" & vbCrLf 'The last column is a date stamp, either way, don't forget to close that parenthesis
            xlRow = xlRow + 1
        Wend

        DBconnection.Execute InsertQuery 'I'll leave any error trapping to you
        DBconnection.Close  'But do be tidy :-)
        Set DBconnection = Nothing
    End Sub
Mind Driver
  • 1
  • 2