How-To Synchronize Data Between Excel and SQL Server

Updated on November 24, 2016

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.

Comments

    0 of 8192 characters used
    Post Comment

    • klanguedoc profile imageAUTHOR

      Kevin Languedoc 

      6 years ago from Canada

      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

    • profile image

      michaes 

      6 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

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)