Export data from Excel to SQL Server

Need to get data from Excel to SQL Server?

Normally this can be achieved by using SSIS. Excel can be used as a source and/or target in a Data flow. But there may be situations where you need more granular control over which data gets imported to SQL Server.

Perhaps there is a process where a user is involved in deciding which data gets inserted to the database, and the import may be initiated be the user. Or maybe some advanced calculations needs to be made to decide which values get inserted.

Anyway, using VBA (Visual Basic for Applications) in Excel macro, it is rather simple to open a connection to a SQL Server database, generate the statements that will select values from the Excel cells and import them to a table. This blog post will show a simple example of how to export data from Excel to SQL Server.

I am using Excel 2013 and SQL Server 2012 in the example.

First, we need a database and a table. In the example I am going to use a simple Customer table with a primary key, and first and last name of the customer:

CREATE DATABASE ExcelDemo
GO

USE ExcelDemo
GO

CREATE TABLE dbo.Customers
	(
		CustomerId VARCHAR(10) NOT NULL CONSTRAINT CustomerId_PK PRIMARY KEY CLUSTERED
		,FirstName VARCHAR(100) NOT NULL
		,LastName VARCHAR(100) NOT NULL
	)
GO

Next, we need a matching “table” in Excel:

Excel To SQL Server VBA Macro Sample Table

We are going to create a button that, when clicked, imports the data from the Excel sheet into the Customer table.

To create a button in Excel, we first need to enable the Developer menu in Excel (if not done already). Open the “Excel Options” menu, and select the “Customize Ribbon” page. Make sure the “Developer” checkbox is set and click “Ok”:

Excel Options Developer Checkbox

Now we can create the button. From the “Developer” menu, select “Insert” and “Button”:

Excel Button

Draw a button anywhere in the sheet and an “Assign macro” dialog will appear. To create a macro for the button, click “New”:

Excel Assign Macro To Button

This will open a code window for Visual Basic for Applications (VBA):

Excel Macro VBA code window

Replace all the code in the window with the code below:

Sub Button1_Click()
    
    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sCustomerId, sFirstName, sLastName As String
 
    With Sheets("Sheet1")
           
        'Open a connection to SQL Server
        conn.Open "Provider=SQLOLEDB;Data Source=ASUSBOOK\SQL2012;Initial Catalog=ExcelDemo;Integrated Security=SSPI;"
           
        'Skip the header row
        iRowNo = 2
           
        'Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = ""
            sCustomerId = .Cells(iRowNo, 1)
            sFirstName = .Cells(iRowNo, 2)
            sLastName = .Cells(iRowNo, 3)
               
            'Generate and execute sql statement to import the excel rows to SQL Server table
            conn.Execute "insert into dbo.Customers (CustomerId, FirstName, LastName) values ('" & sCustomerId & "', '" & sFirstName & "', '" & sLastName & "')"

            iRowNo = iRowNo + 1
        Loop
           
        MsgBox "Customers imported."
           
        conn.Close
        Set conn = Nothing
            
    End With

End Sub

On row 10 above, you need to replace my server and instance (ASUSBOOK\SQL2012) with your own.

We also need to add a reference to “Microsoft ActiveX Data Objects 2.8 Library” from the “Tools” -> “References” menu:

Excel VBA Add Reference

Close the code window and save the Excel workbook. To include the macro in the save, you need to save as “Excel Macro-Enabled Workbook” with an *.xlsm extension.

Now press the button (“Button 1”). If everything works as expected, the “Customers imported” message box should be shown:

Excel Macro Message Box

If you look in the table in SSMS, our customers are imported as expected:

Excel Data To SQL Server Table

Note that this is just a simple demonstration. For simplicity I have hard coded the connection string and there is no error handling.