Import Excel Spreadsheet

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Import Excel Spreadsheet

Import Excel Spreadsheet

Rate Topic

Display Mode

Topic Options

Author

Message

Clkgtrs1

Posted Thursday, September 30, 2010 11:48 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, October 01, 2010 9:46 PM
Points: 10, Visits: 10

Can anyone tell me how to import an Excel spreadsheet into SQL so that the contents of the spreadsheet form an entire table in SQL please?

Post #996439

homebrew01

Posted Friday, October 01, 2010 6:19 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 2,555, Visits: 7,214

Right click your database -> Tasks -> Import Data -> Source: MS Excel -> File Path -> Destination

That gets you to the general area. Then you have a variety of options depending on your needs.

You can also save it as an SSIS package if you will be doing the same tasks frequently.

Post #996578

Clkgtrs1

Posted Friday, October 01, 2010 9:50 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, October 01, 2010 9:46 PM
Points: 10, Visits: 10

Hey Thank you for the help but when I right click the database I don't have the option to import data into my database. I'm running SQL Server Management Studio Express 2005 is there an upgrade that I should perform?

Post #997139

WayneS

Posted Saturday, October 02, 2010 2:59 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235

Clkgtrs1 (10/1/2010)

It should be under the Tasks sub-menu.

You can also use the OpenRowSet / OpenDataSource functions:

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

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!