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:
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”:
Now we can create the button. From the “Developer” menu, select “Insert” and “Button”:
Draw a button anywhere in the sheet and an “Assign macro” dialog will appear. To create a macro for the button, click “New”:
This will open a code window for Visual Basic for Applications (VBA):
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:
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:
If you look in the table in SSMS, our customers are imported as expected:
Note that this is just a simple demonstration. For simplicity I have hard coded the connection string and there is no error handling.
This simple demonstration tries to insert new records into the table each time the macro is run.
But I have been getting a lot of questions like:
* If the customer already exists in the database, how do I update it?
* How can I remove a customer from the database if it is removed from Excel?
So I have expanded this demonstration to use the T-SQL Merge statement instead. Now this solution can handle inserts, updates and deletes that you do in the Excel file. Check out the blog post here!