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:
Thanks Kapil
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?:
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.