We have a situation in our process where we need to update more than one lakh records in Excel file. To handle this, we are using DSN Less Database activities like Connect, ExecuteNonQuery and Disconnect. But the excel provider here is useful for .xls file and can handle rows up to max limit (i.e. 65536).
We are using .xlsx provider also :
“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Sample.xlsx;Extended Properties=”“Excel 12.0 Xml;HDR=YES”";"
But this is not working on our dev machines and gives error: “The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.”
Has anyone come across this situation of updating more than one lakh records in Excel file (.xlsx)?