AKAWN

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',
 @provider=N'Microsoft.ACE.OLEDB.12.0',
 @datasrc=N'c:\test\test.xlsx',
 @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

 

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

 

 

To insert a row I used:

 

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',  
 '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.

 

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