report

How-To Synchronize Data Between Excel and SQL Server

SQL Server 2008 offers many varied technologies for system integration. Developers have several choices ranging from Stored Procedures to Server Objects to address almost any design situation. Using Microsoft Excel as a client, this tutorial will demonstrate how to use a Linked Server from the Server Objects to integrate SQL Server 2008 with Microsoft Excel 2007 (2010) using the new Jet 4.0 OLEDB provider.

Create a Linked Server to Excel from SSMS (SQL Server Management Studio)


Creating a Linked Server Object from SSMS is very easy. The Wizard is well laid out.

Open SQL Server Management Studio, expand the Server Objects in Object Explorer.

Next right-click Linked Servers, and then select New linked server.

In the opened dialog box , select the General page, and enter the following information to setup the Linked Server Object.:

  1. Enter a name for the linked server in the Linked Server field.
  2. For the Server Type select Other data source.
  3. In the Provider list, click Microsoft Jet 4.0 OLE DB Provider if you are using SQL Server 2005 or SQL Server 2000. Otherwise choose Microsoft Office 12 Access Database Engine OLE DB Provider for SQL Server 2008
  4. In the Product name field, type Excel for the name of the OLE DB data source.
  5. In the Data source field, type the full path and file name of the Excel file.
  6. In the Provider string field, enter Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97 workbook. For Excel 2007 enter Excel 12.0
  7. Click OK to create the new linked server

Linking Excel with SQL Server with sp_addlinkedserver

Alternatively you can use the sp_addlinkedserver stored procedure to create the same Linked Server Object. Here is some sample code that you can use:


DECLARE @rc int 

DECLARE @servername nvarchar(128) 

DECLARE @serverproduct nvarchar(128) 

DECLARE @serviceprovider nvarchar(128) 

DECLARE @datasource nvarchar(4000) 

DECLARE @providerstring nvarchar(4000) 



-- Set parameter values 

SET @servername = 'xlsLinkedServer' 

SET @serverproduct = 'Excel'

SET @serviceprovider = 'Microsoft.Ace.OLEDB.12.0' 

SET @datasource = 'c:\temp\Lottery Numbers.xlsx' 

SET @providerstring = 'Excel 12.0' 


EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog


I am using the “” Service Provider for Excel 2007 however if you are using Excel 2003, 2002 or 95 then you should the “'Microsoft.Jet.OLEDB.4.0' “ Service Provider. Also when using Excel 2007 you must the “Excel 12.0” Provider string otherwise use the “Excel 8.0” String for the older versions of Excel.

Using the Linked Server Object to update SQL Server using Excel 2007 data

The first example will use the Linked Server Object “Excel_Lined” that was created with SSMS wizard. To make this example work we will need to create a table for our Lottery Numbers. This is a simple with columns: LotoNr, LotoDate, and LotoName. You copy and paste to following script into a SSMS Query page and execute it if you want to follow along and test thw code. Alternatively you can download the scripts using the URL that is included in the resource section at the back of this book.


The first example will use the Linked Server Object “Excel_Lined” that was created with SSMS wizard. To make this example work we will need to create a table for our Lottery Numbers. This is a simple with columns: LotoNr, LotoDate, and LotoName. You copy and paste to following script into a SSMS Query page and execute it if you want to follow along and test thw code. Alternatively you can download the scripts using the URL that is included in the resource section at the back of this book.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:			Kevin Languedoc
-- Create date:			9/24/2010
-- Description:			Load data using Linked Server: Excel_Linked
-- =============================================
CREATE PROCEDURE dbo.LoadExcelData
	
AS
BEGIN
	
insert into dbo.Loto(LotoDate, LotoNr, LotoName)	
		select convert(datetime,[Date]), convert(char(22),[Winning Numbers]), '6/49' from 		Excel_Linked...[LotoNumbers$]

END
GO

When you compile and execute the Stored Procedure, the contents of the Excel file will be uploaded into the dbo.Loto table. Here is a screenshot of a sampling of the data

The second example will demonstrate how simple it is to update an existing record in SQL Server using data in Excel. For this example I create a new Stored Procedure Title “UpdateLotoUsingExcelData”. The code is show below and can be copied and pasted into a Query page in SSMS like all the examples.


In this example I created a new sheet in Excel called “UpdateLotoNumbers”‘ which contains two columns: “Date” and “NewLotoNumber”. Also notice how you can easily reference the Excel file using the Linked Server Object in the FROM clause like any other database. The WHERE clause contains a reference to the record in to dbo.Loto table that will be updated.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Kevin Languedoc
-- Create date:		9/24/2010
-- Description:		Load data using Linked Server: Excel_Linked
-- =============================================
CREATE PROCEDURE dbo.UpdateLotoUsingExcelData
AS

BEGIN
update dbo.Loto
set LotoNr = xls.NewLotoNumber
from Excel_Linked...[UpdateLotoNumbers$] xls
where LotoDate = '9/18/2010'
END
GO

An important caveat to remember is to always close Excel otherwise you will get error messages that cryptic in nature. The user that is running the SQL database service in your compter or on the server if you are not running SQL Express needs to have read and write permissions on the folder where the Excel is located.

Excel is one of the most used application clients in finance and business intelligence. It is also one of the worst database repositories and shouldn't thus.

More by this Author


Comments 2 comments

michaes 4 years ago

Hello.. thanks for the tutorials. Is there any kind of way I can contact you? Twitter? iMessage? ... SQL Server 2008, Server name: (it chooses my windows login) how can I change it, ive tried everything.. and since its only working on my windows login when I try to link a server an error pops up that says you have to be a member of sysdamin? What should I exactly do? thx


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

You could add your name to the sysadmin role in sql server. I assuming that you are running this database locally? The server name is the name of the server when you installed sql express. You cannot change this name unless you re-install the sql express server.

You can contact me here, I always respond or at kevinlanguedoc(at)gmail.com

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article

    Menu

    Explore