Import Excel Spreadsheet

Question

Post reply

Import Excel Spreadsheet

Clkgtrs1

SSC Veteran

Points: 276
More actions
September 30, 2010 at 11:48 pm

#140840

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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55539 More actions · Answer 1

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.

Clkgtrs1 SSC Veteran Points: 276 More actions · Answer 2

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?

WayneS SSC Guru Points: 95409 More actions · Answer 3

Clkgtrs1 (10/1/2010)
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?

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
Author - SQL Server T-SQL Recipes

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!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

crazy4sql SSCoach Points: 19590 More actions · Answer 4