Windows 10, SQLServer Express (2019), Excel 2016 import to SQLServer error Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

Question

Environment:

• Workstation: 16GB RAM. 500MB SSD

• Windows 10 Pro (64-bit)

• Microsoft Office 2010? 2016? (Home/Student?) Not sure how to tell exactly. Office reports the version as 18.2005.1191.0 ; Excel 2016

• SQL Server 2019 (Express Edition) Just installed 2 weeks ago.

Not available on workstation (though some supporting libraries may be present, e.g. .NET framework 3.5 && 4.8 for Windows 10):

• Visual Studio

• C# compiler

etc.

Task:

• Import a USA-state-government-supplied .csv (converted by me to an Excel spreadsheet) into SQLServer.

The spreadsheet ~20 columns and ~500K rows of data to be imported into 1 fairly simple SQL Server table. No stored procedures. No constraints, 1 Primary (Identity) Key.

Microsoft SQL Server Management Studio query windows limits an insert statement to 1,000 records/rows at a time. I did load 2,000 rows successfully. I do not want to break the Excel spreadsheet into 500 pieces to load the entire data set. So, I thought it would be best to use the database Import and Export Wizard to load the spreadsheet.

Error/roadblock (steps to reproduce):

• Within Microsoft SQL Server Management Studio,

  1. I select the database.

  2. I select Tasks > Import Data ...

  3. I follow the wizard's prompts, selecting Data Source -> Microsoft Excel; Excel file path (path to local spreadsheet file); Excel version -> Microsoft Excel 97-2003 (or Microsoft Excel 2007-2010 or Microsoft Excel 2013 or Excel 2016); First row has column names; Next

• An error is displayed:

  TITLE: SQL Server Import and Export Wizard

  The operation could not be completed.

  ADDITIONAL INFORMATION:

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

How can I fix this?

Additional info:

• ODBC Data Source Administrator (64-bit) Drivers tab has entries for

  1. Microsoft Excel Driver 16.00.4513.1000 (ACEODBC.DLL)

  2. SQL Server 10.00.18362.01 (SQLSVRV32.DLL)

• ODBC Data Source Administrator (32-bit) Drivers tab has entries for

  1. Microsoft Excel Driver 10.00.18362.01 (ODBCJT32.DLL)

  2. SQL Server 10.00.18362.01 (SQLSVRV32.DLL)

Finally:

I saw this: https://datasavvy.me/2017/07/20/installing-the-microsoft-ace-oledb-12-0-provider-for-both-64-bit-and-32-bit-processing/

I tried the steps for Option B, but they did not change my outcome.

Answer 1

Microsoft SQL Server Management Studio launched the 32-bit version of the import wizard.

I found the 64-bit version of the import wizard already installed as a standalone App. Launching that standalone App worked.

• Hannah Vernon: I know it's been 4 months since you left this question and answer, but could you flush this answer out with some details such as where you located the 64 bit version, what is was called, etc?