I wish to import data to a SQL Server from an Excel spreadsheet via SQL Server Management Studio Management Studio (SSMS) 2019's Import Data feature. I have:
- Microsoft SQL Server 2022
- Microsoft SQL Server Tools 19
- SQL Server Management Studio Management Studio 19
- 64-bit operating system, x64-based processor
- Microsoft® Excel® for Microsoft 365 MSO (Version 2309 Build 16.0.16827.20150) 64-bit
When I right click the database I want to import the Excel spreadsheet into, I click tasks and then import data.
That brings me to the SQL Server and Export Wizard.
For Data Source I choose: Microsoft Excel, I choose the Excel File Path, Excel version: Microsoft Excel 2016 (I've tried every version and always get the same error) and then click next. This leads to this error: The 'Microsoft.ACE.0LEDB.12.0' provider is not registered on the local machine. (System.Data)
When I google that error 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
I then try this alternative method, I go to my start menu, scroll to Microsoft SQL Server 2022 and then click "SQL Server 2022 Import and Export Data (64-bit)" as the solution instructs. As soon as I click that this error pops up An error occurred which the SQL Server Integration Services Wizard was not prepared to handle. I click on the show details and this is what pops up: 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)
After I click ok on the error, then the SQL Server Import and Export Wizard pops up, I click next and it brings me to the next page that just says "Merge" and then "Subtitle" underneath it, that screen doesn't do anything so I click the next button and then an error message pops up: "The operation could not be completed. Additional information: The ConnectionString property has not been initialized. (System.Data)".
That's as far as I get, no matter how I try to import the data I continue getting an error. So far I've:
- Deleted SSMS and SQL Server four to five times and redownloaded them.
- Saved the Excel Spreadsheet as: xls, csv, and Excel 97-2003 Workbook
- Redownloaded the original Excel Spreadsheet
- Tried importing data other Excel spreadsheets.
I have screenshots of the process and can provide them but for some reason I can't post them here.