How do I import an excel spreadsheet into SQL Serv

问题:

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

回答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.

回答2:

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$]')

回答3:

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.

回答4:

If your spreadsheet is not organised nicely for a one-to-one import to SQL tables, (eg. formatted for readability with sub-headings, white space, totaliser columns etc), then there are off-the-shelf tools you can use to do this type of thing. See Khronos Excel Pump for an example. DISCLAIMER - I work for the company that sells this. It's pretty configurable so you can keep your spreadsheet in human-layout and still import reliably to structured SQL tables, including replacing text values with identity field values or pivoting wide grids to narrow tables etc.