Sql-server – Transfer data from Excel to table

excelsql serversql-server-2017ssms

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.

enter image description here

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?

Best Answer

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.

Related Question