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
One time: could right click database instance and choose Task-> Import Data. Automatic: build SSIS package and schedule job in SQL server to run ETL process.
This should do...
SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;HDR=NO;Database=T:\temp\Test.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.
To, Import/Export data To/From Excel (.xls) need Microsoft.Jet.OLEDB.4.0 and for Excel 2007 (.xlsx) need 2007 Office System Driver: Data Connectivity Components. You can download from HERE
Import data from Excel to new SQL Server table Excel 2003 (.Xls) file:
select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
Excel 2007 (.Xlsx) file:
Select * into SQLServerTable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')
Import data from Excel to existing SQL Server table Excel 2003 (.Xls) file:
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
Excel 2007 (.Xlsx) file:
INSERT INTO SQLServerTable select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With