[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Fastest Way to Upload Excel Sheet to MSSQL

Posted on 2012-09-05
12
Medium Priority
?
606 Views
Last Modified: 2013-09-03
Okay, I'm trying to upload 300,000+ records using Excel VBA into SQL and I don't want to have to loop through the records one at a time as this takes incredibly too long, I would instead like to reference the sheet in my query somehow to speed the process up.

I was trying to reference a named range or address but I'm not sure how to properly pass the range / values into the query without looping through the records :S

    Dim Connection As ADODB.Connection
    Dim RecordSet As ADODB.RecordSet
    Set RecordSet = New ADODB.RecordSet

    'Open the Connection to Server
    Set Connection = New ADODB.Connection
    Connection.Open "Driver=SQL Server;Server=" & ServerName & ";Database=" & DatabaseName & ";Uid=" & FC_UserName & ";Pwd=" & FC_Password & ";"
    Connection.CommandTimeout = "99999999"

    Dim DataRange As Range
    Set DataRange = ThisWorkbook.Sheets("Data").Range("A2:AE42")
    DataRange.Name = "TempRange"

    RecordSet.Open "INSERT INTO [SERVER].[dbo].[TABLENAME] SELECT * FROM [TempRange]", Connection
0
Comment
Question by:nchadwick
  • 6
  • 4
10 Comments
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38371388
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38371488
There's more examples here: http://support.microsoft.com/kb/306397/EN-US

and also an explanation of creating a more permanent solution by defining a linked server to the excel file, which may be helpful.
0
 

Author Comment

by:nchadwick
ID: 38371711
No, these links do not explain how to properly accomplish sending a sheet tab from Excel VBA to SQL Server in batch form. The first link describes connecting to excel from SSMS or a Stored Procedure, I don't want this initiated server side, but locally, in a local copy of excel, from a sheet in the file, to the SQL server.

I have code already to transfer data line by line, but I need it much much faster in a batch like upload format.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:nchadwick
ID: 38372518
Below is the procedure I currently use to upload data to SQL, it's incredibly slow, I need a way to speed this up, the only way I can imagine would be to initiate a connection to the source and destination in a way that the data could be transferred in a batch-like format. PLEASE any help would be greatly appreciated!!!

Private Sub SQL_TRUNCATE_AND_UPLOAD_TABLE(ServerName, SourceSheet, StartRow, StartCol, LastRow, LastCol, UserID, Password, DatabaseName, DestinationTableName)

    'Important Note: Reference Library: "Microsoft Active X Data Objects 2.X Library" is Required!
    Dim Connection As ADODB.Connection
    Dim RecordSet As ADODB.RecordSet
    Set RecordSet = New ADODB.RecordSet
    Dim MySheet As Worksheet
    Set MySheet = ThisWorkbook.Sheets(SourceSheet)

    Set Connection = New ADODB.Connection
    Connection.Open "Driver=SQL Server;Server=" & ServerName & ";Database=" & DatabaseName & ";Uid=" & UserID & ";Pwd=" & Password & ";"

    'Clear All Records from the Table
    Connection.Execute "TRUNCATE TABLE " & "[" & DatabaseName & "].dbo." & DestinationTableName

    'Upload all records
    RecordSet.Open DestinationTableName, Connection, adOpenKeyset, adLockOptimistic

    'Loop through all rows on the source sheet
    For RowCounter = StartRow To LastRow

        'Add new row
        RecordSet.AddNew

            'Loop through all columns on row
            For ColCounter = 1 To LastCol

                'Set each metric value (column)
                RecordSet(ColCounter - 1) = MySheet.Cells(RowCounter, ColCounter)

            'Next Column
            Next ColCounter

    'Next Row
    Next RowCounter
   
    'Execute a Batch Update
    RecordSet.UpdateBatch

    'Close All Connections
    RecordSet.Close
    Set RecordSet = Nothing
    Connection.Close
    Set Connection = Nothing

End Sub
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38372543
I know it's not exactly what you wanted but it _can_ be used from VBA. I have attached a workbook with VBA code based on the links that should be a lot faster than inserting line by line. Note there are a number of settings you need to do but these can be found on the links posted before and also here: http://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm
test.xls
0
 

Author Comment

by:nchadwick
ID: 38372843
I tried to run the code but I'm receiving "Jet.OLEBD.4.0 cannot be used for distributed queries because the producer is configured to run in single-threaded apartment mode."

I think the error may be related to using JET to access xlsx 2007+ excel files?

Also, I tried:

Connection.Execute "SELECT * INTO [DB].[dbo].[TABLE] FROM OpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=" & FullFileName & ";Extended Properties=EXCEL 12.0')...[Data$]", , 128

But I receive: The OLE DB provider Microsoft.ACE.OLEDB.12.0 has bot been registered

I do have the 2010 Microsoft Access Database Engine installed so I'm not sure why it's not working :(
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38373027
Hmm, I have seen some problems with drivers (JET/ACE) when used with 64 bit machine or 64 bit Office. Can you let me know what you're using in terms of hardware/windows/office version?

In the mean time I'll try it out on a .xlsx file because until now I used an old .xls file.
0
 

Author Comment

by:nchadwick
ID: 38373080
32 Bit WinXP - 32 Bit Office 2010
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38374016
I can't get it to work with ACE at the moment. I was looking back at the alternative code you posted earlier. The 'FullFileName' I hope is not pointing at the current excel book? The driver needs exclusive access that's the reason for the temporary copy in the code I posted earlier. Could you try saving a temporary copy with .xlsx or .xlsm extension and use that for the import? There are also some extra settings needed in SQL server, the info on excel-sql-server.com seems to have all bases covered.
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 2000 total points
ID: 38375199
This version works with the ACE driver. It uses a little function based on code by Ron de Bruin to get the correct file extension. You can save as .xlsx but then I couldn't open the (temp) file in excel, no problem for the ACE driver funnily enough, but still probably better to save with the correct extension. Of the OLEDB options I only had to set the one (Allow inprocess) like in the picture in the first answer here: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/8514b4bb-945a-423b-98fe-a4ec4d7366ea

I'm now on a 64 bit system but SQL server, Office (2007) and ACE driver are all running as 32 bit.
test.xlsm
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question