Read Excel File in SQL Server with OPENROWSET or OPENDATASOURCE


By:   |   Updated: 2019-10-10   |   Comments (10)   |   Related: More > Microsoft Excel Integration

Problem

Sometimes you need to quickly reference the content of an Excel file in a SQL Server T-SQL script, however, this may not be as simple as it seems. In this tip I'll offer solutions for a few of the most common problems that may affect you when trying to read an Excel file using OPENROWSET or OPENDATASOURCE with SQL Server.

Solution

Let’s try to read a simple Excel file from within T-SQL code using OPENROWSET and OPENDATASOURCE

First of all, let me describe a little bit about my environment:

  • Windows Server 2012 R2 Standard virtual machine with SQL Server 2016 SP2-CU7 (13.0.5337.0) default instance.
  • A very simple Excel file (simple.xslx) which consists of 2 columns and 3 rows of numeric data. I did not need to install Excel on the virtual machine.
  • For the first few examples I’ll use a sysadmin account and Windows authentication. My windows account is a member of the Administrators of the machine.
  • The samples will work with Microsoft.ACE.OLEDB.12.0. and Microsoft.ACE.OLEDB.16.0.

For the examples I’ll use this simple piece of code to test each option.  The Excel file resides in the "C:\data" folder.

--OPENROWSET
SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
   'Excel 12.0 Xml;Database=C:\data\simple.xlsx;', Sheet1$);
 
--OPENDATASOURCE
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 
   'Data Source=C:\data\simple.xlsx;Extended Properties=EXCEL 12.0')...[Sheet1$];

Before you have any luck, you may encounter one of the error messages below.

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered

If you try to run one of the above commands you may get this error message:

Msg 7403, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

This message will also pop up if you try to setup a linked server to the Excel file (when you "browse the tables").

linked server ole db error

Or if you try to import the data using the SQL Server Import and Export Wizard.

import export wizard ole db error

The above errors state that the Microsoft Access Database Engine has not been setup on the machine.  There are several ways you can check if this provider is installed on your machine.

Method 1 - Expand Server Objects > Linked Servers > Providers in SSMS and you should see in the list of providers.

Method 2 - From an account that is at least a member of the setupadmin server role run:

EXEC sys.sp_enum_oledb_providers

Method 3 - Run this basic PowerShell code on the server:

foreach ($provider in [System.Data.OleDb.OleDbEnumerator]::GetRootEnumerator()){  for ($i = 0; $i -lt $provider.FieldCount; $i++){
        Write-Host $provider.GetName($i), $provider.GetValue($i) |Format-List
    }
Write-Host
 
} 

If there is no "Microsoft.ACE.OLEDB" in your results you’ll have to download and install the Microsoft Access Database Engine 2010 Redistributable or the Microsoft Access Database Engine 2016 Redistributable. Both engines let you read .xlsx files and they both have a 32bit and 64bit version, so download and install what you need for your server.

After the installation "Microsoft.ACE.OLEDB.12.0" or "Microsoft.ACE.OLEDB.16.0" will appear in the liked server provider list and in the PowerShell script output (as SOURCES_NAME) depending on which version you install.  If you install the 2016 version, both of these will show up.

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource'

Once you have the Microsoft Access Database Engine components installed and you try to run the T-SQL code you might get this error message.

Msg 15281, Level 16, State 1, Line 1
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

You need to turn on the advanced server option "Ad Hoc Distributed Queries". You'll need a sysadmin / serveradmin account or the ALTER SETTINGS server-level permission to make the change.

EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.

The next error you might encounter is the error below from either the OPENROWSET or OPENDATASOURCE option.

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

This happens because the OLE DB provider does not work as an in-process server.  To fix this either use the GUI (right click on the provider name and choose Properties) and check the "Allow inprocess" box or run the following code. 

If you want to use the 2010 version run the following:

EXEC master.[sys].[sp_MSset_oledb_prop] N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

If you want to use the 2016 version run the following:

EXEC master.[sys].[sp_MSset_oledb_prop] N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1

At this point, if you use a sysadmin account and run the code to read the Excel file you should be able to see the content.

Using OpenRowset or OpenDatasource as a non-privileged SQL Server User

Let’s try to read our simple Excel file using a non-administrative Windows account.

The "RegularUser" is only a member of the Users group on the local machine. The corresponding SQL login is member of the public server role. Currently "RegularUser" has "Read & Execute", "List Folder Contents" and "Read" rights on the folder where the file is situated.

The other settings are as before - the installed OLE DB provider is allowed to run "in process" and the SQL server option "Ad Hoc Distributed Queries" is "on".

Let’s try this for the regular user account:

EXECUTE AS LOGIN = '<machine_name>\RegularUser'

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0 Xml;Database=C:\folder\simple.xlsx;', Sheet1$); 

REVERT;

The result will be:

Msg 7415, Level 16, State 1, Line 23
Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.

The fix is not obvious. The ACE OLEDB provider properties are in fact registry keys. When you set a property to 1 with [sys].[sp_MSset_oledb_prop] or tick a box in the GUI you create the corresponding registry key and set its value to 1. When you set the property to 0 using these methods you don’t set the key value to 0. Instead, you remove the registry key. See the below portion of code from sp_MSset_oledb_prop, this is from line 103 and on, you can see where the value is being written or the key is being deleted:

if 1 = @property_value
   begin
      declare @val int
      set @val = @property_value
      exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', @regpath, @property_name, REG_DWORD, @val
   end
   else
   begin
      exec xp_instance_regdeletevalue N'HKEY_LOCAL_MACHINE', @regpath, @property_name
   end

However, you need a "0" registry key in order to be able to read the file.

The straightforward way to achieve this is to run:

EXEC master.[sys].[sp_MSset_oledb_prop] N'Microsoft.ACE.OLEDB.12.0', N' 'DisallowAdHocAccess'', 1

and edit the registry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.13.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.12.0 to set the key value to 0. Again, as a "regular user", you’ll need the appropriate permissions.

If you want to avoid this, just execute the OPENROWSET / OPENDATASOURCE as an administrative Windows login.

Next Steps
  • As you can see ad-hoc Excel querying is not as simple as it seems. Since the error messages can get really confusing, please be sure to check:
    • Whether the Microsoft ACE OLEDB provider(s) are installed on the host machine
    • Whether the "Ad Hoc Distributed Queries" SQL server option is "on"
    • The properties of the ACE OLEDB provider you’re using
    • The privileges of your account both in SQL SERVER and Windows
    • The syntax of your script and the spelling of the file name and path
  • You may consider that a better option is to import the data into a SQL Server database using BCP, import wizard, SSIS, etc.


Last Updated: 2019-10-10


get scripts

next tip button



About the author
MSSQLTips author Diana Moldovan Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, January 06, 2020 - 3:01:02 PM - Greg Back To Top

Thanks so much for the helpful article. We have been using this method for years on our web server that also hosted the SQL Server. Upon splitting SQL to a 2nd machine, we can't get past the  "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.

It works when I check "InProcess" within SSMS, but we are trying to run this from the web server, executing the SPs that read the XLSX file into a temp table.

It doesn't work from the web server calling the procedure OR from SSMS when I uncheck InProcess.

Any suggestion on getting past this?  Tried several security options (folder permissions for the OS User running SQL) found on other posts but that hasn't helped.


Monday, December 16, 2019 - 12:25:05 PM - Jeff Back To Top

Hi Diana, thank you very much for these tips. I'm attempting this on a test db on my local machine before trying it on the server. I'm on Win10 64 and I beleive I've followed all the steps, I now see both the OLEDB.12.0 and OLEDB.16.0 and have run the AllowProcess for both. But when I attempt the OPENROWSET below, I am now seeing this error: 

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified 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)".

Here is the command I'm running from SSMS:

SELECT *

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

   'Excel 12.0 Xml;Database=C:\Users\xxxx\Documents\Forecast.xlsx;', SheetName$);

Thanks for any ideas you can provide!


Tuesday, October 29, 2019 - 4:23:31 PM - Diana Moldovan Back To Top

@pds - SQL x64 won't allow the x86 driver to run "in process"; therefore you can't use OPENROWSET. Try to do a "quick & easy" import into a staging file. The "Import flat file" option (available in the recent SSMS versions v17~) is easier than the "classic" wizard, save the Excel as .csv first.


Monday, October 28, 2019 - 10:32:43 AM - Joseph Morgan Back To Top

This is a very nice and informative tip! Stepping through each layer of frustration and whacking it with a great big smart-sword was a good way to present the information. I am bookmarking this one for sure!


Monday, October 28, 2019 - 8:46:43 AM - AVG Back To Top

@pds - BULK INSERT is one option. I do recall someone found a way to install the 64bit driver, however, it is not recommended for a production server. Sorry, I don't have the link, but I'm sure you can find it, as well as other import suggestions, with a web search.


Sunday, October 27, 2019 - 1:02:28 AM - pds Back To Top

SQL Server 64bit requires the 64bit version of the ACE driver. If the machine has 32bit Office installed (which is the MS recommended version), then the 64bit ACE driver can't be installed.

I am having this issue and i tried few options but couldn't resolved. Any suggestions?


Tuesday, October 22, 2019 - 3:48:13 PM - Diana Moldovan Back To Top

@AVG - thank you...sorry, I've quit the 32 bit environments years ago, it just "slipped"...


Tuesday, October 22, 2019 - 7:56:02 AM - Alan Back To Top

I have nothing ggood to say about importing data from Microsoft Excel as Microsoft in their infinite wisdom decides for the user what the format of the data is and often gets it wrong. That data type decision is made base on the first few rows of data, yes it can be change to sample more rows but it does not solve the problem. One can include a quote in the data to make numerical data appear as text because that is the way you the user wants it, not the best solution. Myself I wrote a C# program that adds a row of 'A' to the spreadsheet after the header row which causes the translation on import to correctly identify the data as text, then I remove the row of 'A' from the table where I imported the data, also not the best solution but it works and is simple. Microsoft needs to bring back the ability to create a data template that defines the correct data types for a file import.


Thursday, October 17, 2019 - 6:27:10 AM - AVG Back To Top

Excellent article!! Wish I had this a few years ago when I had to set it up. There is only one issue that you did not discuss. SQL Server 64bit requires the 64bit version of the ACE driver. If the machine has 32bit Office installed (which is the MS recommended version), then the 64bit ACE driver can't be installed.


Friday, October 11, 2019 - 1:34:59 PM - Ivonne Back To Top

Muy bueno!!!



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools