Query and update an Excel 2010 spreadsheet via a linked server

This took a while to get working so I thought I’d list the steps taken to achieve manipulating an Excel 2010 spreadsheet via a linked server.


The SQL Server I used was SQL Server 2008 R2 (64-bit) and the Excel 2010 spreadsheet had the below data:



First  I downloaded and installed the ‘AccessDatabaseEngine_x64.exe’ file on the SQL Server from http://www.microsoft.com/download/en/details.aspx?id=13255. No restart was required.


Next I started SQL Server Management Studio as ‘Administrator’.



Next I enabled the ‘Allow inprocess’ option for the Microsoft.ACE.OLEDB.12.0 provider.



The linked server was created with this script:


EXEC master.dbo.sp_addlinkedserver
 @server = N'test',
 @srvproduct=N'ACE 12.0',
 @provstr=N'Excel 12.0;HDR=Yes'



I could now query the spreadsheet with:


select * from test...[sheet1$]



To add data I used:


insert into test...[sheet1$] values ('server2','blue')


To modify data I found that I had to first enable the below:



Then to update a record I used


  'Excel 12.0;DATABASE=c:\test\test.xlsx', 'Select * from [Sheet1$]')
  SET type = 'green' WHERE server = 'server2'



To insert a row I used:


 'Excel 12.0;Database=C:\test\test.xlsx;',
 'SELECT server,type FROM [Sheet1$]')
 VALUES ('server3','yellow')



To delete 1 row I used the below work around which set the column values to null.


 'Excel 12.0;DATABASE=c:\test\test.xlsx',
 'SELECT server,type from [Sheet1$]')
 SET server = null, type = null
 WHERE server like 'server2'



Leave a Reply