Caveman's Blog

My commitment to learning.

SQL Server: Import data from Excel

with 3 comments


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

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

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:
1. Server Settings Configuration Options

Advertisements

3 Responses

Subscribe to comments with RSS.

  1. Hi,
    just wanted to add that I too love to use openrowset for importing data from excel spreadsheets, but it could be of value to mention that the information above is of no use if your SQL-server 2005 environment is of 64-bit. Openrowset is not supported and probably never will in SQL-server 2005/2008 64-bit.

    Tobias Persson

    September 9, 2009 at 3:19 am

  2. Absolutely UNBELIEVABLE that OPENROWSET is not supported in SQL2005/2008 64-bit….Did I say UNBELIEVABLE? INCREDULOUS!!!

    Catherine Bell

    October 15, 2009 at 12:15 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: