How to Import Excel 2003 32 bit file to SQL 2008 32 bit

sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; GO

Get the latest ACE OLE DB Priovider
https://www.microsoft.com/en-us/download/confirmation.aspx?id=54920


Related errors:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Solution1 

Change the TEMP and TMP variables on the machine to a public location, allow all users full access

(Right click my computer>properties>Advanced>Environment Variables> Change the paths)

Original Post
http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx


For 32-Bit Excel, and SQL use
--------------------------------------------------------------
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;HDR=YES;Database=C:\temp\test.xls',
    'select * from [sheet1$]')




If you continue to get the error, try installing the 2007 data access components. A Full install of office should work just as well.
Run windows update and install all service packs
http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

If you install the 2007 Data Access Components, you can use this command to open the files.
Both methods will work.

SELECT * FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\test\test.xls;HDR=Yes', 'Select * from [sheet1$]' )