how to Import Excel Sheet data in SQL server

Question

Hi all,

I have one excel sheet with 1 work sheet . could you any body help how to load that excel sheet in to sql server 2008 using T-sql code.

please send the code ,it very urgent

Thanks

Answer 1

Check this out from http://support.microsoft.com/kb/321686

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
				

Your other options are using SSIS or SSMS's Import/Export wizard.

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

Answer 2

begin
create table #t(col1 datatype,col2 datatype)
insert into #t(col1,col2)
SELECT col1,col2 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;Database=C:\data.xlsx;HDR=Yes', 'Select * from [Sheet1$]')
select * from #t
drop table #t
end

Sri krishna

Answer 3

Make sure you check the providers available on your system. While either of these solutions will work, they both require that you have the proper drivers installed to provide the service to SQL. You can find the Providers under the Linked Servers tab in SSMS, or by using master.dbo.xp_enum_oledb_providers

---

duncan davenport . data engineer and architect