Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data > SQL Server – Import Data from Excel using T-SQL

SQL Server – Import Data from Excel using T-SQL

To import data from an Excel file to SQL Server you can use SQL Server Import and Export Wizard. You can also import Excel data using T-SQL OPENROWSET function. OPENROWSET function can be used to import data from and OLEDB data source.

For this example I have created a simple excel file which has a single row of data and column headers, and the sheet is named [Employees] as below:

image

I have stored this file in my C:\ root folder.

 

Now to import this data to SQL Server you can use OPENROWSET as below:

SELECT *

FROM   OPENROWSET('Microsoft.ACE.OLEDB.12.0',

       'Excel 12.0 Xml;HDR=YES;Database=C:\Emp.xlsx',

       'SELECT * FROM [Employees$]')

Result Set:

image

As you can see this is very easy and straight forward. However, the above query only displays data from Excel file, it does not import it to database.

To import this data to SQL Server Table you can modify the SELECT statement to SELECT INTO

USE [SqlAndMe]

GO

 

SELECT * INTO dbo.ImportedEmployeeData

FROM   OPENROWSET('Microsoft.ACE.OLEDB.12.0',

       'Excel 12.0 Xml;HDR=YES;Database=C:\Emp.xlsx',

       'SELECT * FROM [Employees$]')

GO

The above statement will create a new table ImportedEmployeeData and import excel data to the table.

 

Although this method seems straight forward, there are a few errors you may encounter:

 

Error Message 1:

Msg 7399, Level 16, State 1, Line 2

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 7303, Level 16, State 1, Line 2

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Cause 1: You may receive this error if the Excel file is currently opened.

Solution 1: Close the Excel file if it is currently opened and try executing again.

 

Error Message 2:

Msg 15281, Level 16, State 1, Line 2

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server.

 

A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

Cause 2: The cause of this error is mentioned in the error message. You may receive this error message if Ad Hoc Distributed Queries are not enabled by system administrator.

Solution 2: You can use sp_configure to enable this component. Since this is an advanced option you also need to enable 'Show Advanced Options' to enable this component. Use below commands to enable this:

EXEC sp_configure 'Show Advanced Options', 1

RECONFIGURE

GO

 

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

RECONFIGURE

GO

Result Set:

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

 

Error Message 3:

Msg 7302, Level 16, State 1, Line 1

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Cause 3: You may receive this error message if registry settings are not set properly

Solution 3: To resolve this error, run below commands to fix registry issues:

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

GO

 

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

GO

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. Mike
    April 18, 2013 at 8:25 pm

    Where can I find documentation for the different parameters for OPENROWSET? Also, I’ve heard that this doesn’t work for different O/S versions – if this is the case, do you have any troubleshooting tips for working around that?

  2. d
    April 25, 2013 at 5:13 am

    I get this error
    Msg 7308, Level 16, State 1, Line 1
    OLE DB provider ‘Microsoft.ACE.OLEDB.12.0′ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

  3. Gyula Kulifai
    November 8, 2013 at 3:24 pm

    The OLE DB provider “Microsoft.ACE.OLEDB.10.0″ has not been registered.

    MS Office 2010; MS SQL 10.0.5512; MS Win 7 ent

  4. Ramesh
    November 14, 2013 at 2:14 pm

    OPENROWSET() function needs OLE DB drivers installed. If the right driver is not installed you will see error something like following one.

    Msg 7302, Level 16, State 1, Line 1Cannot create an instance of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”.
    To check the driver is installed or not open ODBC Data Source Administrator (Start > Run > type ODBCAD32.EXE and hit enter) and check under the Drivers tab. The following screenshot shows the both drivers JET 4.0 for Excel 97-2003 and new ACE Driver for Excel 2007.

    In the absence of required drivers you can download the same from the Microsoft’s site.
    Excel 97-2003 JET 4.0 driver
    Excel 2007 ACE driver
    Excel 2010 ACE driver (beta)

  1. No trackbacks yet.

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

Follow

Get every new post delivered to your Inbox.

Join 270 other followers

%d bloggers like this: