September 22, 2011
An OLE DB provider is a high level abstraction interface that allows to access data contained inside a simple text file, an excel sheet or other kind of documents that are not necessarily accessible through a language such as SQL. This technology was designed by Microsoft in order to replace the well known ODBC standard. Interestingly, they recently announced that the Redmond company now would align back with ODBC for Native Relational Data Access!
Whoever as worked with OLE DB and excel files knows that it easily becomes a big pain ! Most of the time, I try to avoid it but in specific scenarios it becomes unavoidable. If you want to easily and quickly dump data from an excel file into a table with a single SQL statement, it goes as follow:
INSERT INTO tblMySpecialData
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\HTTP\Excel\MyDataShhet.xls;HDR=YES;IMEX=1',
'SELECT [NAME], [ADRESS] FROM [Sheet1$]')For the record: “HDR=Yes;” indicates that the first row contains column names, not data and “IMEX=1;” tells the driver to always read “intermixed” data columns as text. You need to know the name of the sheet in order to read the data from it. I use ADO.NET to get the name of the first sheet and pass it over to the stored procedure.
No error was thrown when this command was executed from within the stored procedure but it just wouldn’t work once executed from MSSQL Server Management Studio! I’m running a Windows 7 PC and the issue was solved opening the Management Studio with Administrative rights! Hope it helps someone :)