Microsoft.Jet.OLEDB.4.0 error on Azure Cloud Servi

Question

My requirement is to upload excel file to the folder which is on website root and then read this file's data into the datatable.

This is working fine on my existing hosting provider. But now I've uploaded my website on Windows Azure cloud service. After porting to Azure cloud service I'm getting an error on "Microsoft.Jet.OLEDB.4.0" provider.

I have use the “Microsoft.Jet.OLEDB.4.0” provider for read data from the excel file and add data to datatable. It work fine on local too, but when I host my web application on azure cloud service it generate following error

“Microsoft.Jet.OLEDB.4.0 provider is not registered on the local machine”

Please have a look on some line of codes:

string strpath = Server.MapPath(OAppPath);
                    strpath = strpath + "\\MYDATAFOLDER\\" + System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName);

                    FileUpload1.PostedFile.SaveAs(strpath);

                    string excelConnectionString = "";
                    excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strpath +
                                            ";Extended Properties=\"Excel 8.0;;IMEX=1;HDR=yes\"";
                    var connection = new OleDbConnection(excelConnectionString);
                    connection.Open();
                    var dtSheets = new DataTable();
                    dtSheets = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    GC.Collect();
                    GC.WaitForPendingFinalizers();

What is alternate solution of this?

I looked on following too:

Link 1 Link 2

Thanks Kapil

Answer 1

This is probably caused because your application is running on 64 bits mode. According to Microsoft, The Microsoft OLE DB Provider for Jet and the Microsoft Access ODBC driver are available in 32-bit versions only.

As described in How to get a x64 version of Jet?:

The Microsoft Jet Database Engine 4.0 components entered a state of functional deprecation and sustained engineering, and have not received feature level enhancements since becoming a part of Microsoft Windows in Windows 2000.

One alternative is to use a startup task in your cloud service to deploy the Microsoft Access Database Engine 2010 Redistributable. You'll have to change your connection string to the formats supported by this new driver.

Another approach, which I have used with success, would be to use a library such as ExcelDataReader to read the spreadsheet data.