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:

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:

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

Filed under: SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data