Transfer data from Excel to table

Question

How can I transfer information from an Excel file to a table in a SQL server database through SSMS?

Excel file Consists of five columns and a thousand rows.

I followed the steps as explained here,at second 00:52 I can not continue because of the following error message :

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

I tried to export the excel file to different versions of Microsoft(2003, 2007, 2010) but the problem was not solved.

Is there any other way to solve this problem?

Answer 1

You must install the Microsoft Access Database Engine 2016, as Justin Onstot already wrote (https://www.microsoft.com/en-us/download/details.aspx?id=54920), and ALSO you MUST run the correct version of Import/Export application (32 or 64 bit) to be successful. You have a 64-bit SQL Server, so you must install the 64-bit Access Database Engine and perform the following steps:

• Go to Start Menu -> Microsoft SQL Server 2017 -> SQL Server 2017 Import and Export Data (64-bit)
• Open the application and try to import data using the "Excel 2016" option, it should work fine.

Hope this helps.

Answer 2

Looks like you need to install the Microsoft Access Database Engine 2016 Redistributable available at https://www.microsoft.com/en-us/download/details.aspx?id=54920. You'll need to choose either the 32-bit or 64-bit executable depending on your environment. This was the fix for me when I got this error using the SSIS import/export wizard with 2016.

Answer 3

Save the excel as a CSV and import it as a flat file

Answer 4

Follow these steps:

1. Go here, download Microsoft Access Database Engine 2016 Redistributable and install
2. Close SQL Server Management Studio
3. Go to Start Menu -> Microsoft SQL Server 2017 -> SQL Server 2017 Import and Export Data (64-bit)
4. Open the application and try to import data using the "Excel 2016" option, it should work fine.

Hope this helps.