The Brave Programmer - Blogging and coding
Not for the faint hearted
 

Blog Posts From The Brave Programmer

Minimize

Importing and using Excel data into MS SQL database

Jun21

Written by:
2009/06/21 02:56 PM RssIcon

I have been asked many times over the years how to import data from Excel into MS SQL database. I myself have had many scenarios where I have had to export or import data from MS Excel into MS SQL. There are many ways of achieving this goal. But sad to say, many people do not know all of them and opt to retype or recapture the data. Sometimes, depending on the Excel Spreadsheet structure you have to type it in. This can be a difficult and laborious task.

Fortunately, MSSQL has a few methods of accessing other data.

  • You can export the data into a format that MSSQL can understand like MS Access. But this also has it's problems and is very time consuming.
  • You can cut-‘n-paste the data. In my experience this works well but has limited success, and you have to know what you’re doing.
  • You can use DTS or SSIS by using the import wizard to create a DTS or SSIS package.
  • You can create a linked server
  • You can use OpenRowset or OpenDatasource.

In this post we will be concentrating on OpenRowset and OpenDatasource.

Enable OpenRowset and OpenDatasource.

For this to work you have to enable the use of OpenRowset and OpenDatasource. To do this run the SQL Server Surface Area Configuration Tool. Then select Surface Area Configuration for Features. In the dialog box that appears, expand your server tree and the database engine tree. Then select the Ad Hoc Remote Queries branch. Make sure the Enable Openrowset and Opendatasource checkbox is checked as in the picture below.

SQL-Openrowset-enabled

Note: Be aware of the security implications of allowing Openrowset and Opendatasource

OpenRowset

The OPENROWSET feature in SQL Server and SQL Express provides a fast and easy way to open an OLE DB compatible data source, such as an Excel sheet or dBASE data, directly from your SQL script. Coupled with the "SELECT * INTO" command, the OPENROWSET feature can import data from your other data sources into a table in SQL Server or SQLExpress. You may even want to just use the data in a view or join.
Openrowset should be used in cases when access in not required on a regular basis, i.e.. as in ad hoc queries. When data is accessed several times, a linked server should then be used.


The OpenRowset function in its simplest form carries the following syntax.
OPENROWSET (provider_name,provider_string,query_syntax)

For more information on Openrowset visit the Microsoft MSDN Library here.

So to open a MS Excel 97-2003 file use something like this, making sure the Excel file is on a drive and path accessible by the SQL server with sufficient rights:

select * FROM OPENROWSET(
  'Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=D:\SQL Data\RHC\Book3.xls;HDR=YES',
  'SELECT * FROM [DatQuestionaire$]') a
inner join datQuestion b on a.Questionaireid = b.questionaireid

For Excel 2007 we just use a different OLEDB driver.

select * FROM OPENROWSET(
  'Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=D:\SQL Data\RHC\Book1.xlsx;HDR=YES',
  'SELECT * FROM [Sheet1$]')

Note: You might receive this error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
If so a quick work around is import the following registry file to make it work:

(Here is the file content)

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0]
"AllowInProcess"=dword:00000001
"DynamicParameters"=dword:00000001

The execution might be a bit slow at first, but once the query plan is built execution is pretty descent.

OpenDatasource

Opendatasource is similar to Openrowset above. It allows ad hoc connection and querying to an external data source, such as Excel via installed drivers. Opendatasource syntax is slightly different than Openrowset.
OPENDATASOURCE ( provider_name, init_string )

For more information on Opendatasource go to the Microsoft MSDN Page here.

To open an excel file using Opendatasource you might use something like this:

SELECT *
FROM
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:\SQL Data\RHC\Book2.xls;
Extended Properties=''Excel 8.0''')...[Sheet1$]

Remember for Excel 2007 use a different driver:

SELECT *
FROM
OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\SQL Data\RHC\Book1.xlsx;
Extended Properties=''Excel 12.0''')...[Sheet1$]

Queries

Now you can use any SQL query. Perhaps you want to limit the Excel data with a where clause. Use Openrowset. (Note: I haven't tested every single query), as in:

select * FROM OPENROWSET(
  'Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=D:\SQL Data\RHC\Book1.xlsx;HDR=YES',
  'SELECT * FROM [Sheet1$] where [ID] > 20')

How about joining two Excel spreadsheets with in the Excel worksheets together like this:

select * FROM OPENROWSET(
  'Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=D:\SQL Data\RHC\Book3.xls;HDR=YES',
  'SELECT * FROM [DatQuestionaire$] a inner join [datQuestionaire2$] b
on a.QuestionaireId = b.QuestionaireId where a.QuestionaireID >20

') Excel

Now lets make this a bit more interesting. Take that Excel Openrowset query and join that to an existing MS SQL table like this:

select * FROM OPENROWSET(
  'Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=D:\SQL Data\RHC\Book3.xls;HDR=YES',
  'SELECT * FROM [DatQuestionaire$] a inner join [datQuestionaire2$] b
on a.QuestionaireId = b.QuestionaireId where a.QuestionaireID >20
') Excel
inner join datQuestion b on Excel.[a.Questionaireid] = b.questionaireid

So as you can see, your imagination can run wild. No longer does your Excel data have to be side-lined. No longer do you have to pull your hair out trying to figure out how to access your Excel data.


But what if you just want to import the data and start over. Well we make use of a very nice statement, select into … Which will create a brand new table for us.

select * into MyOpenRowsetTable FROM OPENROWSET(
  'Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=D:\SQL Data\RHC\Book3.xls;HDR=YES',
  'SELECT * FROM [DatQuestionaire$] a
    inner join [datQuestionaire2$] b
    on a.QuestionaireId = b.QuestionaireId
') a

So there you have it. Working with Excel data in MS SQL. In fact you can use any other driver this way to access other data. Like  plain text files, dBASE data, etc. Enjoy and have fun. No more pulling your hair out.

Tags:
Categories:
blog comments powered by Disqus

2 comment(s) so far...


Gravatar

Re: Importing and using Excel data into MS SQL database

Thanks Robert! One of those posts which makes something really hard seem easy!

By Mike CJ on  2009/06/22 04:44 PM
Gravatar

Re: Importing and using Excel data into MS SQL database

MikeCJ,

Always a pleasure. Things always seem easier once you know how. Hopefully you now know how.

By Robert Bravery on  2009/06/22 05:16 PM
 
Blog Updates Via E-mail
 Blog Updates Via E-mail
Minimize

Do you want to receive blog updates via e-mail. Then just click on the link below. You will be redirected to Google's feed burner, where you can fill out a form. Supplying your e-mail address.

The subscription is managed entirely by Google's Feedburner. We cannot and do not collect your email address.

Subscribe to The Brave Programmer by Email

Print  
 

 

Latest Comments
 Latest Comments
Minimize
Powered by Disqus

Sign up with Disqus to enjoy a  surprise box of features

Print  
 
Blog Roll
 Blog Roll
Minimize
Print  
 
Categories
 Categories
Minimize
Print  
 
<h1>Search Blogs From The Brave Programmer</h1>
 

Search Blogs From The Brave Programmer

Minimize
Print  
 
Archive
 Archive
Minimize
Archive
<December 2024>
SunMonTueWedThuFriSat
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234
Monthly
Go
Print  
 
<h1>News Feeds (RSS)</h1>
 

News Feeds (RSS)

Minimize
Print  
 

Follow robertbravery on Twitter

Blog Engage Blog Forum and Blogging Community, Free Blog Submissions and Blog Traffic, Blog Directory, Article Submissions, Blog Traffic

View Robert Bravery's profile on LinkedIn

Mybyte

 

Robert - Find me on Bloggers.com

Tags
 Tags
Minimize
Print  
 
Contact Us Now
 Contact Us Now
Minimize
 

Email  us now or call us on 082-413-1420,  to host your website.

We design and develop websites. We develop websites that make a difference. We do Dotnetnuke Module development.

Web Masters Around The World
Power By Ringsurf
Print