SQL Server – Import Data From Excel

This article will demonstrate one of the several ways of importing data into a SQL Server table from an Excel spread sheet. I will use a simple SQL query to import data instead of SSIS/DTS. The precursor to achieving the data transfer will be to perform a onetime operation via, enabling the running of Ad Hoc Distributed Queries on your SQL Server database. This can be accomplished by using the SQL Server 2005 Surface Area Configuration Utility or by executing the following code in SQL Server Management Studio’s Query Editor/Query Analyzer:

  
 

sp_configure ‘show advanced options’, 1

GO

reconfigure

GO

sp_configure ‘Ad Hoc Distributed Queries’, 1

GO

reconfigure

 

sp_configure ‘show advanced options’, 1

GO

reconfigure

GO

sp_configure ‘Ad Hoc Distributed Queries’, 1

GO

reconfigure

When the server is capable of running Ad Hoc Distributed Queries we are all set to run SQL queries to import data from an MS-Excel spreadsheet. It must kept in mind that an Excel file with all the intended columns and data has to exit, before data can be imported (also the file must be closed when data is transferred). In the following code sample, data from the spreadsheet “EMP” in the Excel file, will be imported into the “employee” table in the SQL Server database using the OPENROWSET.

–Export data to Excel

select * into Employee from openrowset(‘Microsoft.Jet.OLEDB.4.0’ ,

‘Excel 8.0;Database=c:\test\test.xls’,’Select * from [EMP$]’)

 

–Export data to Excel

select * into Employee from openrowset(‘Microsoft.Jet.OLEDB.4.0’ ,

‘Excel 8.0;Database=c:\test\test.xls’,’Select * from [EMP$]’)

In the near future I will demonstrate how to import multiple spreadsheets into SQL Server database using SSIS, via dynamic discovery of all available spreadsheets in one Excel file.

References:
http://cavemansblog.wordpress.com/2009/07/06/sql-server-import-data-from-excel/
http://msdn.microsoft.com/en-us/library/ms189631.aspx
http://support.microsoft.com/kb/321686