Cannot import excel files to SSMS, but possible using SQL Server Import and Export Data

Randy Adhitama 21 Reputation points
2021-08-13T07:55:03.983+00:00

Hi All,

I have issue to import the excel files to SSMS. I used:

SSMS : v18.9.2
SQL Server 2017 64 bit
Microsoft 365 (64bit)
Windows 10 Enterprise 64 bit
Installed:

  • Access database engine x64 12.0, 15.0, 16.0

Everytime I want to import excel files via SSMS, it shows error while I believe I already installed the access database engine
122870-image.png
Also the same, when I using excel version 2007-2010 or 2013 to import.

The more weird things are, it is possible to do it from "SQL Server 2017 Import and Export Data (64 bit)". But it painful way to do every data import using those apps and not in SSMS itself.

Any solutions?

Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,927 questions
{count} votes

Accepted answer
  1. Olaf Helper 41,571 Reputation points
    2021-08-13T08:32:45.073+00:00

    SSMS is a plugin for Visual Studio and that is a 32 bit application; 64 bit version isn't available.
    And so SSMS starts the 32 bit wizard and the 32 bit wizard requires a 32 bit ACE data provider to work.

    In SSM menu "Option" => "External tools" you can create a shortcut to start the 64 bit wizard.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-08-13T09:05:58.907+00:00

    Hi @Randy Adhitama ,

    To fix this,

    First thing first, install “Microsoft Access Database Engine 2016 Redistributable.”
    https://www.microsoft.com/en-us/download/details.aspx?id=54920

    And then also check the version of Excel and SSMS. They need to be same, like 32 vs 32 or 64 vs 64
    SSMS is a 32 bit client, if your Microsoft Excel is 64 bit. you may need to install the 64 bit version of the Microsoft Access Database Engine 2016 Redistributable and try opening – the 64-bit “SQL Server Import and Export Data” directly. Go to Start Menu -> Microsoft SQL Server 2017( your SQL server version) -> SQL Server Import and Export Data (64-bit)> Open the application and try to import data using the “Excel 2016” option, it should work fine.

    If it is not work, please try the other solutions from the blog How to Fix The “Microsoft.ACE.OLEDB.16.0 Provider is not registered on the local machine.”


    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

    1 person found this answer helpful.

  2. Jake Gray 25 Reputation points
    2023-02-15T18:34:30.6666667+00:00

    doesnt work anymore. Can anyone offer another solution to this?

    Ive seen this same solution on every forum and I have tried to do this over and over with absolutely no change in error message or success in any capacity.

    1 person found this answer helpful.