Import Excel spreadsheet columns into SQL Server database

Question

I have an Excel spreadsheet that I want to import select columns into my SQL Server 2008 database table. The wizard didn't offer that option.

Do any easy code options exist?

Answer 1

Once connected to Sql Server 2005 Database, From Object Explorer Window, right click on the database which you want to import table into. Select Tasks -> Import Data. This is a simple tool and allows you to 'map' the incoming data into appropriate table. You can save the scripts to run again when needed.

Answer 2

Microsoft suggest several methods:

• SQL Server Data Transformation Services (DTS)
• Microsoft SQL Server 2005 Integration Services (SSIS)
• SQL Server linked servers
• SQL Server distributed queries
• ActiveX Data Objects (ADO) and the Microsoft OLE DB Provider for SQL Server
• ADO and the Microsoft OLE DB Provider for Jet 4.0

If the wizard (DTS) isn't working (and I think it should) you could try something like this http://www.devasp.net/net/articles/display/771.html which basically suggests doing something like

INSERT INTO [tblTemp] ([Column1], [Column2], [Column3], [Column4])  SELECT A.[Column1], A.[Column2], A.[Column3], A.[Column4] FROM OPENROWSET  ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Excel.xls;HDR=YES', 'select * from [Sheet1$]') AS A;