Reading / Writing Excel from SQL Server

sample code from a buddy of mine at work -- this is pretty cool and will definitely come in useful at some point.

/*
Online info:
http://serendipitous-stuff.blogspot.com/2010/07/import-excel-xlsx-into-64-bit-sql.html

 

64-bit drivers:
http://www.microsoft.com/downloads/en/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

 

*/

 

-- Required configuration steps:
sp_configure 'show advanced options', 1 -- EQ's ntsql1dev was already 1.
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1  -- EQ's ntsql1dev was 0.
reconfigure
go
-- sp_configure 'show advanced options', 0  -- Skipped this.
-- reconfigure
go
USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess' , 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

 

USE PLT_AI

 

-- Samples from online somewhere
 INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Text;Database=F:\scripts\;HDR=Yes;', 'SELECT * FROM Company.xls')
 SELECT company_id, company_name, address_1, address_2, address_3, phone, epa_id, phone_customer_service FROM Company
 -- Fail.  Seems the openrowset args are bad here.

 

 
-- Another sample... test Reading:
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=NO;Database=F:\Scripts\Company.xls','SELECT * FROM [Sheet1$]') 
-- Works!!

 

-- Test Writing that way:
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=NO;Database=F:\Scripts\Company.xls','SELECT * FROM [Sheet1$]')
SELECT company_id, company_name, address_1, address_2, address_3, phone, epa_id, phone_customer_service FROM plt_ai..Company
-- No errors!! 

 

-- Re-read it:
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=NO;Database=F:\Scripts\Company.xls','SELECT * FROM [Sheet1$]') 
-- WWOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO!!!!!!!!!!!!!