Import Excel spreadsheet columns into SQL Server database

Solution 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.

Solution 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;

Solution 3:

This may sound like the long way around, but you may want to look at using Excel to generate INSERT SQL code that you can past into Query Analyzer to create your table.

Works well if you cant use the wizards because the excel file isn't on the server

Solution 4:

You could use OPENROWSET, something like:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
  'Excel 8.0;IMEX=1;HDR=NO;DATABASE=C:\FILE.xls', 'Select * from [Sheet1$]'

Just make sure the path is a path on the server, not your local machine.

Solution 5:

go
sp_configure 'show advanced options',1  
reconfigure with override  
go  
sp_configure 'Ad Hoc Distributed Queries',1  
reconfigure with override  
go
SELECT * into temptable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Documents and Settings\abhisharma\Desktop\exl\ImportExcel2SQLServer\ImportExcel2SQLServer\example.xls;IMEX=1',
                'SELECT * FROM [Sheet1$]')

select * from temptable