I would like to import data from an Excel file to a table and tried to do the following but it's giving me an error...
It's using this command syntax:
'Excel 12.0 Xml;
The error I'm getting is:
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
In my case, the path to the Excel file is a remote server with an IP address and shared folder, for example, \100.70.33.72\ExcelFiles\EmpData.xlsx.
I am remotely connected (RDP) to a database server, 100.70.33.99, for example, which hosts the database where the table I wish to insert to exists and where the above command is being executed. This server is on 64-bit MS Windows Server 2012 R2 Standard OS with SQL Server 2014 Standard Edition 64-bit installed. I have an SSMS 2014 session open on this server in which I am issuing the command.
Does MS Office 2007 or higher need to be installed in the database server (100.70.33.99 in the example) in order to be able to have the Microsoft.ACE.OLEDB.12.0 provider available for use by the OpenRowset function?
What else might I be missing?
Thank you so much!