tsql - Code to read xlsx sheet into a table in a SQL Server database

Question

I am trying to read data from an Excel sheet (.xlsx file) into a table in SQL Server 2008. I want this to be run everyday as a batch job and hence want to write SQL code in a stored procedure to do so.

Could someone help me? I have admin rights.

~TIA

Answer 1

This should do...

SELECT *
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 8.0;HDR=NO;Database=T:empTest.xlsx',
    'select * from [sheet1$]')

But we aware, sometimes this just wont work. I had this working for local admins only.

There is a way to do this using SSIS as well.