We often get Excel files with content to import into SharePoint. Most of the time I fire up Visual Studio and write a small console application to import the Excel data. However with SharePoint 2010 I got the error “The ‘Microsoft.Jet.OLEDB.4.0′ provider is not registered on the local machine”.
The idea
When I need to import content into SharePoint using the oledb data is far the easiest way to read Excel files without having to install office on your SharePoint server.
You simply add an OleDbConnection , an OleDbCommand, an OleDbDataAdapter and a correctly formulated select statement.
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Import\Sample.xslx; Extended Properties=""Excel 8.0;HDR=Yes"";";
string CommandText = "SELECT * FROM [Services$]";
OleDbConnection connection = new OleDbConnection(ConnectionString);
OleDbCommand command = new OleDbCommand(CommandText, connection);
connection.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
DataSet dataset = new DataSet();
adapter.Fill(dataset, "Excel");
The cause
SharePoint 2010 is 64 bit so you need to compile your custom SharePoint code as a 64-bit applications. However the default Jet Library does not support 64-bit applications and thus results in the following error:
The ‘Microsoft.Jet.OLEDB.4.0′ provider is not registered on the local machine.
Solution
Luckily Micosoft has released a solution in the form of the Microsoft Access Database Engine 2010 Redistributable
- Download the Microsoft Access Database Engine 2010 Redistributable
- Install the package on your development machine
- Modify your OleDb Connection string from:
Provider=Microsoft.Jet.OLEDB.4.0; ….
to
Provider=Microsoft.ACE.OLEDB.12.0;…. - rebuild your application
note: make sure your application is set to compile as a 64 bit application
Don’t forget to install this redistibutable on your SharePoint server as well.
