How do I use the SQLBulkCopy Class to transfer data from Excel to SQL server

Posted on 2008-11-11
Last Modified: 2013-06-04
I have seen a ton of information on using the SQL BulkCopy Class to transfer data from Excel to SQL server but for the life of me I am just not getting it. I have tried to change the code that I have found but to no avail. I am brand new to SQL and I am using the Express edition that comes with the latest version of Visual Studio. Okay here is what I am trying to do. I have an old Access db that I need to transfer to a SQL backend. All of the data that I need to move into the SQL tables are in Excel spreadsheets. I have a SQL table called Collinfo that has 8 columns (QID - primary key - int, CompID - foreign key - int, CollQ - varchar (500), UnAnc - varchar (500), AAnc - varchar (500), EAnc - varchar(500), ExperienceReq - bit, Selected - bit). I need to take the data from an Excel spreadsheet with the exact same column names and blast it into SQL. I have over 500 rows in the Excel files so I don't want to recreate this thing by hand. What I need to know is step by step how do I make this happen. I am new so please leave nothing out - I need to know things as trival as where to paste the code in SQL (e.g. new class or new query) - think trying to teach a monkey to drive a car. Thanks - any help is much appreciated.
Question by:jazzj
    LVL 81

    Accepted Solution

    Here is code I use to dump a table of data into a SQL Server table. It assumes there is no header row and the fields are in the same order. It also assumes the table starts in cell A1 on the active sheet.

    Private Const TargetServerName As String = "Server1"
    Private Const TargetDatabaseName As String = "Database1"
    Private Const TableName As String = "Table1"

    Public Sub WriteTable()
        Dim TargetDatabase As ADODB.Connection
        Dim TargetRecordset As New ADODB.Recordset
        Dim Record As Long
        Dim Field As Long
        ' Open target database connection
        Set TargetDatabase = New ADODB.Connection
        TargetDatabase.CursorLocation = adUseClient
        TargetDatabase.Open "Provider=SQLOLEDB;Data Source=" & TargetServerName & ";Initial Catalog=" & TargetDatabaseName & ";Integrated Security=SSPI"
        ' Delete existing records
        TargetDatabase.Execute "DELETE FROM dbo." & TableName
        ' Open target recordset
        TargetRecordset.Open TableName, TargetDatabase, adOpenDynamic, adLockPessimistic
        ' Copy records
        For Record = 1 To ActiveSheet.UsedRange.Rows.Count
            For Field = 1 To TargetRecordset.Fields.Count
                TargetRecordset.Fields(Field - 1) = ActiveSheet.Cells(Record, Field)
            Next Field
        Next Record
        ' Close recordset
        ' Close database

    End Sub


    Author Comment


    Thanks for the quick response. Where do I put this code in SQL. Do I paste it into a new query?  if not where and how? Also I am assuming I just need to have the Excel spreadsheet open but don't need to specifically reference the Excel file name - is that correct? Does the following look correct for my specific situation"

    "Server 1" = RYN\sqlexpress
    "Database1" = Collinfo.dbo
    "Table1" = Collinfo_T

    Thanks for the help.

    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    The code is VBA code and is placed in the Excel workbook, not SQL Server.

    To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

    To run a macro from Excel, select the menu command Tools->Macro->Macros or press ALT+F8. A dialog box appears listing all available macros. Find the desired macro and select it. Click the Run command button to start the macro.


    Author Comment

    That explains alot. Okay I ran it and it gives me the following error message.

    Compile Error: User defined type not defined
    it indicates the Dim TargetDatabase As ADODB.Connection line

    Author Comment


    Scratch that. I selected the activeX library from the references and that seem to take care of the problem. Now the error I get is this:

    Multiple Step Operation Generated Errors. Check each Status Value.

    Author Comment

    This is the line where the error occurs:

    TargetRecordset.Fields(Field - 1) = ActiveSheet.Cells(Record, Field)

    I have checked my data types in SQL and make sure they match so not sure where to go from here.

    Author Comment


    you're awesome. Your code worked perfectly. A couple things I needed to do in SQL to get it to work:

    - delete my primary designations and reset the identity specification
    - change some data types for text data

    Expert Comment

    Was just curious if  anyone has sample code that does this same thing - but in DAO instead of ADO?  And from an Access 2007 database instead of Excel?   I'm just needing  to load a table or query that is in Access to a table in the SQL Server but with speed of the SQLBulkCopy class.   The conventional Access append query is just too slow.  And 'Pass-Through' Append queries will not work with Access queries or tables when your attempting to append data from them.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now