I cannot import excel documents into SQL Server Management Studio.

Question

I cannot import excel documents into SQL Server Management Studio.

Jake Gray 30

It wont let me upload screenshots for some reason but I open SSMS,

I create a database

I try to import an Excel document into SSMS by right clicking the database, selecting Tasks, and import Excel.. . I then select my excel worksheet document and try to upload when I receive this error:

TITLE: SQL Server Import and Export Wizard

The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)

So I went online and possible troubleshooting solutions are to download the Microsoft Access 2016 or 2013 or 2010 versions and the 32 or 64 or 84 bit versions and I tried individually with every single version with no change.

I saw that I should use the Microsoft SQL Server Import Export tool 64 bit from the start menu and when I try to import that way, I get the following Error Message:

TITLE: SQL Server Import and Export Wizard

ADDITIONAL INFORMATION:

Could not load file or assembly 'Microsoft.DataTransformationServices.ScaleHelper, Version=16.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. (DTSWizard)

Keith Banbury 20 Reputation points

2024-06-14T10:45:23.1933333+00:00

I too can't import Excel files using the SSMS Wizard. I get the following error message

The 'Microsoft.ACE.OLEDB.15.0' provider is not registered on the local machine. (System.Data)

I am running Office 2013 that doesn't include 'Access' on Windows10. I have SSMS v20, my Office apps were installed as 32bit but I have reinstalled as 64bit now.

I have tried a number of the options in the threads but so far nothing has worked. I have attempted to install SSIS v1.4 and v1. For some reason although they download they don't install even when run as admin.

This is what I see in SQL

SQLOLEDB {0C7FF16C-38E3-11d0-97AB-00C04FC2AD98} Microsoft OLE DB Provider for SQL Server

Microsoft.ACE.OLEDB.12.0 {3BE786A0-0366-4F5C-9434-25CF162E475E} Microsoft Office 12.0 Access Database Engine OLE DB Provider

Microsoft.ACE.OLEDB.16.0 {3BE786A2-0366-4F5C-9434-25CF162E475E} Microsoft Office 16.0 Access Database Engine OLE DB Provider

ADsDSOObject {549365d0-ec26-11cf-8310-00aa00b505db} OLE DB Provider for Microsoft Directory Services

MSOLEDBSQL {5A23DE84-1D7B-4A16-8DED-B29C09CB648D} Microsoft OLE DB Driver for SQL Server

Search.CollatorDSO {9E175B8B-F52A-11D8-B9A5-505054503030} Microsoft OLE DB Provider for Search

MSDASQL {c8b522cb-5cf3-11ce-ade5-00aa0044773d} Microsoft OLE DB Provider for ODBC Drivers

MSOLAP {DBC724B0-DD86-4772-BB5A-FCC6CAB2FC1A} Microsoft OLE DB Provider for Analysis Services 14.0

MSDAOSP {dfc8bdc0-e378-11d0-9b30-0080c7e9fe95} Microsoft OLE DB Simple Provider

I suspect that missing SSIS is the issue but just don't know.
Yitzhak Khabinsky 26,586 Reputation points

2024-06-14T14:15:37.9233333+00:00

@Keith Banbury,

Please open a brand new question.

Accepted answer

1 additional answer

Your answer

Keith Banbury 20 Reputation points

2024-06-14T10:45:23.1933333+00:00

I too can't import Excel files using the SSMS Wizard. I get the following error message

The 'Microsoft.ACE.OLEDB.15.0' provider is not registered on the local machine. (System.Data)

I am running Office 2013 that doesn't include 'Access' on Windows10. I have SSMS v20, my Office apps were installed as 32bit but I have reinstalled as 64bit now.

I have tried a number of the options in the threads but so far nothing has worked. I have attempted to install SSIS v1.4 and v1. For some reason although they download they don't install even when run as admin.

This is what I see in SQL

SQLOLEDB {0C7FF16C-38E3-11d0-97AB-00C04FC2AD98} Microsoft OLE DB Provider for SQL Server

Microsoft.ACE.OLEDB.12.0 {3BE786A0-0366-4F5C-9434-25CF162E475E} Microsoft Office 12.0 Access Database Engine OLE DB Provider

Microsoft.ACE.OLEDB.16.0 {3BE786A2-0366-4F5C-9434-25CF162E475E} Microsoft Office 16.0 Access Database Engine OLE DB Provider

ADsDSOObject {549365d0-ec26-11cf-8310-00aa00b505db} OLE DB Provider for Microsoft Directory Services

MSOLEDBSQL {5A23DE84-1D7B-4A16-8DED-B29C09CB648D} Microsoft OLE DB Driver for SQL Server

Search.CollatorDSO {9E175B8B-F52A-11D8-B9A5-505054503030} Microsoft OLE DB Provider for Search

MSDASQL {c8b522cb-5cf3-11ce-ade5-00aa0044773d} Microsoft OLE DB Provider for ODBC Drivers

MSOLAP {DBC724B0-DD86-4772-BB5A-FCC6CAB2FC1A} Microsoft OLE DB Provider for Analysis Services 14.0

MSDAOSP {dfc8bdc0-e378-11d0-9b30-0080c7e9fe95} Microsoft OLE DB Simple Provider

I suspect that missing SSIS is the issue but just don't know.
Yitzhak Khabinsky 26,586 Reputation points

2024-06-14T14:15:37.9233333+00:00

@Keith Banbury,

Please open a brand new question.

Answer 1

Yitzhak Khabinsky 26,586

Hi @Jake Gray,

Let's start with checking what Microsoft ACE OLEDB provider 64-bit edition is installed. Please issue the following commands in the SSMS:

EXEC master.sys.sp_enum_oledb_providers;

SELECT @@VERSION;

And share the outcome.

You should see MS ACE OLEDB Providers installed like below.

Microsoft ACE providers

Please try the following in SSMS, obviously adjusting both (1) your MS Excel fully qualified file name, and (2) Excel sheet name:

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
   'Excel 12.0 Xml; HDR=NO; IMEX=1;
    Database=x:\<fully qualified path>\fafa.xlsx',
    [Sheet1$]);

Jake Gray 30 Reputation points

2023-02-15T22:12:40.5233333+00:00

This is the result
Jake Gray 30 Reputation points

2023-02-15T22:13:59.87+00:00

oh this is with the second line
Yitzhak Khabinsky 26,586 Reputation points

2023-02-15T22:15:21.0166667+00:00

What about SELECT @@VERSION;
Yitzhak Khabinsky 26,586 Reputation points

2023-02-15T22:16:08.9266667+00:00

Please connect with me on LinkedIn.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-02-15T22:19:41.3866667+00:00

Which version of SSMS do you have? Since you have SQL 2022, I would recommend SSMS 19.

Is SQL Server running on the same machine as you are running SSMS from?
Jake Gray 30 Reputation points

2023-02-15T22:20:23.5+00:00

it is SQL 2022 with SSMS 19
Yitzhak Khabinsky 26,586 Reputation points

2023-02-15T22:28:43.65+00:00

Are they both SQL Server 2022 and SSMS 19 running on the same machine?
Jake Gray 30 Reputation points

2023-02-15T22:33:43.18+00:00

Yes they are
Yitzhak Khabinsky 26,586 Reputation points

2023-02-15T22:34:18.5733333+00:00

I updated the answer. Check it out.
Yitzhak Khabinsky 26,586 Reputation points

2023-02-15T22:45:47.2033333+00:00

@Jake Gray,

Did you have a chance to try the SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',... statement in the Answer?
Jake Gray 30 Reputation points

2023-02-15T23:04:35.4466667+00:00
Hey there, im kind of a noob at this so can you give me an example of what goes where?

So fully qualified path = The excel doc name I am trying to import?

and Sheet = ?

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml; HDR=NO; IMEX=1; Database=x:\<fully qualified path>\fafa.xlsx', [Sheet1$]);
Yitzhak Khabinsky 26,586 Reputation points

2023-02-15T23:18:34.1866667+00:00

@Jake Gray,

To expedite the process, please connect with me on LinkedIn.
Johnmichael Monteith 0 Reputation points

2023-03-22T16:57:10.8533333+00:00

I have seen this problem listed in multiple forums and none appear to have a genuine solution. I, too, have this same issue and none of the solutions listed appear to work. Is it possible we can keep the conversation within the forum until we have an answer that everyone looking for the solution can use? For my part, I have installed the "Microsoft Access Database Engine 2016 Redistributable" and that did not make a difference. There are some forums that say (with nearly zero instruction) to start it from a command line (start "what" exactly, what would be a sample command?) or adjust the external tools option in SSMS - once again, without any instruction / example or exactly what we are pointing it to.
Yitzhak Khabinsky 26,586 Reputation points

2023-03-22T20:46:03.01+00:00

@Johnmichael Monteith,

Please open a brand question.

Answer 2

Seeya Xi-MSFT 16,586

Hi @Jake Gray ,

Please refer to this similar thread:

https://techcommunity.microsoft.com/t5/sql-server/sql-server-import-and-export-wizard/m-p/3718957

The solution mentioned is to install "integration services".

Best regards,

Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Johnmichael Monteith 0 Reputation points

2023-03-22T16:47:50.2433333+00:00

That appears to be a completely different issue from the one described in this post.

Share via

I cannot import excel documents into SQL Server Management Studio.

1 additional answer

Your answer