Having problems importing excel worksheet into SQL Server Management Studio

Question

My laptop is windows and my system type is 64-bit. I have the newest version of SSMS (19.1) downloaded and when I try to import the data into my database I get the error

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

When I copy that into google this solution pops up https://social.msdn.microsoft.com/Forums/en-US/3d9ad79d-b154-4218-ae0b-d5bd6747ee56/sql-server-import-and-export-wizard-the-39microsoftaceoledb12039-provider-is-not?forum=sqltools.

However when I try that solution from the start menu I get this error:

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

After I hit OK the SQL Server Import and Export Wizard window pops up but then it sits on a screen that says "Merge, Subtitle" and hitting next I get a message saying

The ConnectionString property has not been initialized. (System.Data).]

Please help.

I tried the solutions that were posted online for similar problems and I continued to get error messages.

Answer 1

I would restart your machine, attempt to load it again. If it still gives you those errors, attempt starting from the install path using command prompt.

Run DTSWizard.exe from C:\Program Files\Microsoft SQL Server\160\DTS\Binn for the 64-bit version.

If that still fails I would uninstall SSMS, reboot and attempt a fresh install again of SSMS.

Alternative solution could be to save your excel file as a CSV and import using the text file driver for import/export utility.

Answer 2

I am in the same boat as you as well and it appears that importing data to SSMS latest version via Import and Export wizard tool is an open issue yet to be solved. The workaround is to import data directly from SSMS,

right clicking your current Database (e.g, YourDB)>Tasks>Import Data>select the relevant data source option (I selected 'Microsoft Excel' as I am importing data from an excel file)>select Destination option as 'Microsoft OLE DB Provider for SQL Server'