Hi
I have been having the same issue and found a solution for reading Data in from Excel Files.
To achive this,
The Sheet name in Excell must not have spaces,
The Directory the file is in must be accessible to the SQL Server Service user Account
The 'Ad Hoc Distributed Queries' Advanced SQL Config option must be enables
USE: SP_CONFIGURE 'show advanced options',1
RECONFIGURE WITH OVERRIDE
SP_CONFIGURE 'Ad Hoc Distributed Queries',1
RECONFIGURE WITH OVERRIDE
Then Create the following Stored Procedure in your DB:
CREATE PROC stp_ReadXLS@file varchar(1000)
,@template varchar(300)
,@hashtable varchar(300) = 'loader_table'
,@excell_version varchar(2) = '8'
AS
DECLARE @SQL_T varchar(4000)
EXECUTE AS login ='Sup_sp_exec'
Begin Try
SET @SQL_T = 'SELECT * INTO ##'+@hashtable+' FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel '+@excell_version+'.0;Database='+@file+''', ['+@template+']) '
EXEC (@SQL_T)
END TRY
BEGIN Catch
SET @SQL_T = 'INSERT INTO ##'+@hashtable+' SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel '+@excell_version+'.0;Database='+@file+''', ['+@template+']) '
EXEC (@SQL_T)
END Catch
PRINT ('Populated ##'+@hashtable+' table.....')
The Stored Proc Will read in the file and create a ## TABLE with the Contents. IF you Run the SP again, it will ADD new records to the same table, so that you could collate multiple XLS files into one.
Also, you have the option to Work with Newer and older Excel files by providing the @excell_version parameter with the version number.
DON'T FORGET TO DROP THE ## TABLE AFTER YOU ARE FINISHED.
Hope this helps.