Answered by:
how to Import Excel Sheet data in SQL server

Question
-
Answers
-
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- Marked as answer by dotnet.sql Tuesday, March 6, 2012 10:23 PM
Tuesday, March 6, 2012 1:11 AM
All replies
-
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.
- Edited by Arbi Baghdanian Tuesday, March 6, 2012 1:08 AM
- Proposed as answer by Eshani Rao Tuesday, March 6, 2012 1:12 AM
Tuesday, March 6, 2012 12:57 AM -
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- Marked as answer by dotnet.sql Tuesday, March 6, 2012 10:23 PM
Tuesday, March 6, 2012 1:11 AM -
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
Tuesday, March 6, 2012 3:00 AM