共用方式為

PowerPivot Table Import Wizard cannot find provider

  • 發行項

The data source provider list in PowerPivot can often be a source of confusion for users since they equate the fact that a provider appears in the list as the provider being installed and available. Unfortunately, the list of providers is actually a static list of supported data sources for PowerPivot, so the user is still required to install the desired provider to successfully import data into PowerPivot. Thus, the most common fix for a "provider is not installed" error in the import wizard is to ensure you have the proper data provider installed and that the installed provider matches the platform architecture (32-bit or 64-bit) of PowerPivot and Excel.

If you are certain that the selected provider is installed on your client machine and are able to import data directly into Excel using the desired provider via the Data tab, then you may be encountering another issue which was recently discovered.

In this new scenario data import in PowerPivot will fail for any provider selected. The exact error seen varies depending on the provider selected but examples include:

Text File:  "Details: Failed to connect to the server. Reason: Provider information is missing from the connection string"

Excel:   "Cannot connect to the data source because the Excel provider is not installed."

SQL Server: "Cannot connect to the data source because the SQLServer provider is not installed."

 

The problem is actually due to a problem with the .NET machine configuration. PowerPivot attempts to instantiate providers by using the .NET DbProviderFactory class. If an error is encountered while instantiating the DbProviderFactory class, the error for the DbProviderFactory is not returned, instead the message returned is that the selected provider is not installed. If you are encountering this scenario it is very likely that there is a problem instantiating the .NET DBProviderFactory class.

The DbProviderFactory class configuration is read from the Machine.Config.xml file, which depending on whether you are running the 32-bit or 64-bit version of Excel and PowerPivot is located at:

c:\Windows\Microsoft.NET\Framework\v4.0.30319\Config

or

c:\Windows\Microsoft.NET\Frameworkx64\v4.0.30319\Config

Checking the Machine.Config.xml file you will find the <DBProviderFactories> element under <system.data>.  The <DBProviderFactories> element should only appear once, but problematic machines may have more than one XML tag for DbProviderFactories.

Example of bad element list:
<system.data>
<DbProviderFactories>

        <add name="Microsoft SQL Server Compact Data Provider"invariant="System.Data.SqlServerCe.3.5"description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>

 </DbProviderFactories>
<DbProviderFactories/>
</system.data>

NOTE: The begin and end tag around the add for the SQLServerCE provider, followed by the empty element tag.

Correct Example:

 <system.data>
<DbProviderFactories>

        <add name="Microsoft SQL Server Compact Data Provider"invariant="System.Data.SqlServerCe.3.5"description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>

 </DbProviderFactories>
</system.data>

NOTE: The add element(s) between the open <DbProviderFactories> and close </DbProviderFactories> tags will vary depending on what providers are installed on your machine.

If you find that you have something similar to the bad example above, please use the following steps to resolve the issue:

  1. Make a backup copy of existing machine.config.xml file in the event you need to restore it for any reason.
  2. Open the machine.config.xml file in notepad or another editor of your choice.
  3. Delete the empty element tag <DbProviderFactories/> from the file.
  4. Save the updated file.
  5. Retry the import from PowerPivot 

 

Wayne Robertson - Sr. Escalation Engineer

Comments

  • Anonymous
    March 19, 2014
    Thank you!!! this worked for me on windows 7 excel 2010 power pivot v11.

  • Anonymous
    April 08, 2014
    This worked like a charm! Thanks a ton!

  • Anonymous
    July 01, 2014
    I am using Excel 2010 on Windows 8.1. There is no machine.config.xml file - only a machine.config file - I have extensions viewing turned on. Not sure if this is the same file but on Windows 8.1 but would like to get this problem solved.

  • Anonymous
    January 14, 2015
    The comment has been removed

  • Anonymous
    March 04, 2015
    This solution worked for me but the file name was just "machine.xml". Windows 7, Excel 2013 Pro. Thanks, JP

  • Anonymous
    August 17, 2015
    When I go to the file path c:WindowsMicrosoft.NETFrameworkv4.0.30319Config there is no Machine.Config.xml. I only have NetFx40_IIS_schema_update. It doesn't show in a search either. Any other place Machine.Config.xml could be?

  • Anonymous
    November 09, 2015
    Thank you! This worked for me on 32-bit Windows 7 Excel 2013 power pivot!

  • Anonymous
    July 21, 2016
    above is done, but show"Access is denied"

  • Anonymous
    July 28, 2017
    I honestly tried this on a whim, but after launching the config file as an admin, it gave me the access to edit the file, and when I retried to import via PowerPivot, it worked perfectly! Thank you again!

    • Anonymous
      July 28, 2017
      Also, this was on 32-bit Excel 2016 for Office 365