locked
how to Import Excel Sheet data in SQL server RRS feed

  • 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

    Tuesday, March 6, 2012 12:51 AM

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.


    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