How do I import an excel spreadsheet into SQL Server?

Question

I need to import an excel spreadsheet into SQL Server 2005. What steps would I need to take to accomplish that?

Answer 1

The "Data Transformation Services" wizards are your friend.

The instructions here assume SQL Server 2000

• Open SQL Server Enterprise Manager
• Right-click on the appropriate server
• Select Import Data
• Run through the wizard.
  • Click Next on the first screen
  • Choose "Microsoft Excel " as your datasource
  • Enter the path to the XLS file and click next.
  • Enter the connection details for your database and click next.

Depending on what you want to do with the data, you have a few options new. For one-off jobs I generally find it easiest to import all the excel data, then edit it in SQL Server, but if you're going to repeat this action on multiple files, you might want to craft a really sexy import script.

If you are going to run it again, you can save the DTS package using the wizard, then edit it in the "Data Transformation Services" section of Enterprise Manager. It's a good way to learn how DTS works.

Answer 2

An alternative quick and (very) dirty solution is to add a formula to the excel sheet, like this:

="INSERT INTO table1(col1, col2, col3) SELECT " & A1 & ", '" & B1 & "', '" & C1 & "'"

Copy this down (CTRL+D) and you're good to go.

Answer 3

you can also do it with OPENROWSET

INSERT INTO SOMETABLE SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet1$]')