How-To Synchronize Data Between Excel 2007 and SQL Server 2008 | OPENROWSET

For this next set of examples, I am going to use OPENROWSET to update my Excel file with data from SQL Server 2008. Before I get to the examples I am going to modify the Music table that I initial created in the previous example “How-To Synchronize data in Excel with Linked Server”. I am going to add a couple of columns: Release Date, Album and Decade.

Actually the Release Date column will store just the year that the song was released. Album is the name of the album where the song first appeared and Decade is 2 digit Char to represent the decade.

Here is the new table definition. You can copy and paste directly into a SSMS query and execute it to change the table.

UPDATE [BISolutions].[dbo].[Music]
  SET [Song Title] = <Song Title, nvarchar(255),>
     ,[Artist] = <Artist, nvarchar(255),>
     ,[Release Date] = <Release Date, char(4),>
     ,[Album] = <Album, varchar(150),>
     ,[Decade] = <Decade, char(2),>
WHERE <Search Conditions,,>
GO


Updating an Excel spreadsheet Worksheet is very easy with OPENROWSET. You can use data from an existing table to update specific column values in your Excel spreadsheet worksheet column or you can pass values from call a stored procedure or hard code the values that you want to change.

This first minimalistic example will use a hard coded value to update the song “You Give Love A Bad Name” by Bon Jovi. I am going to insert the year the song was released. As you can see from the code below that updating the worksheet entry is just like updating any other table.

First define what database and table you want to update. In our case this is the “music” table (Worksheet) in our music.xlsx database (Excel file) as define like this :

OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\temp\music.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";', 'Select * from [music$]')

Be careful when specifying the Worksheet (table name). You need to append the “$” to the end of the name you have given your Worksheet and the complete name has to be enclosed in square brackets “[“ “]”. The rest of the code is a standard UPDATE

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Kevin Languedoc
-- Create date: 9/25/2010
-- Description: Updating Records in Excel from SQL Server 2008 using Openrowset
-- =============================================
CREATE PROCEDURE dbo.UpdateExcelRecordsWithOpenrowset

AS
BEGIN
UPDATE OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\temp\music.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";', 'Select * from [music$]')
SET [Release Date]='1986'
WHERE [Song Title]='You Give Love A Bad Name'


END
GO

Of course I could have used input parameters instead of hard coding the values like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Kevin Languedoc
-- Create date: 9/25/2010
-- Description: Updating Records in Excel from SQL Server 2008 using Openrowset
-- =============================================
CREATE PROCEDURE dbo.UpdateExcelRecordsWithOpenrowset
@yr char(4),
@song varchar(150)
AS
BEGIN
UPDATE OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\temp\music.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";', 'Select * from [music$]')
SET [Release Date]=@yr
WHERE [Song Title]=@song


END
GO

Or used a table as a source of information

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Kevin Languedoc
-- Create date: 9/25/2010
-- Description: Updating Records in Excel from SQL Server 2008 using Openrowset
-- =============================================
CREATE PROCEDURE dbo.UpdateExcelRecordsWithOpenrowset

AS
BEGIN
UPDATE OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\temp\music.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";', 'Select * from [music$]')
SET xMusic.[Release Date]=dbo.Music.[Release Date]

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\temp\music.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";', 'Select * from [music$]') xMusic

INNER JOIN dbo.Music dbMusic

ON xMusic.[Song Title] = dbMusic.[Song Title]

END
GO

The same can be said about inserting data into Excel using OPENROWSET. Here is a quick and simple example just to point you in the right direction

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: klanguedoc
-- Create date: 10/01/2010
-- Description:
-- =============================================
CREATE PROCEDURE dbo.InsertRecordsWithOPENROWSET
@Song varchar(100),
@Artist varchar(100),
@Release char(4),
@Album varchar(150),
@Decade char(2)

AS
BEGIN

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\temp\music.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES";', 'Select * from [music$]')
([Song Title],Artist,[Release Date],Album,Decade)

values (@Song, @Artist, @Release, @Album, @Decade)



END
GO