Reading Excel via OLEDB gives null values for formatted text

While reading Excel using OLEDB, if any formatting has been done on the excel sheets, the OLEDB driver is unable to read some values which results in getting null values.

OLEDB connection string in code:

var conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=" + fileNameWithPath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";");

IMEX=1 reads the excel.

you can google out for more details on IMEX and HDR values.

This connection string is prefect when there is no formatting done on the excel sheet. 

For reading the whole excel with any formatting on excel, one registry entry needs to be changed:

Run->RegEdit->Computer->HKEY_LOCAL_MACHINE->SOFTWARE->WOW6432Node->Microsoft->Office->12.0->Access Connectivity Engine->Engines->Excel

Change the settings TypeGuessRows=0, by default this is set as 8.

NOTE: if the connection states Excel12.0, then the reg entry of "Office->12.0" needs to be changed, else it will not work

After googling for 2 days, finally found the solution here: 

http://righthelp.xyz/question/3726720/values-changing-when-importing-from-excel-with-c-oledb

who found the answer here

Thanks to this anon blog poster and to Andrew Garrison who saved my day!! 

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Today, I got struck with a very strange issue, while reading Excel file using OLEDB driver for MS Access, my code worked perfectly on my development system but on posting on live server, I got a very strange error. "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine." 

Note: MS Office not installed on the server

I had downloaded the same link:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

http://www.microsoft.com/download/en/confirmation.aspx?id=23734

I found the root cause when I read this blog:

http://stackoverflow.com/questions/15538774/microsoft-ace-oledb-12-0-provider-is-not-registered-but-it-is

I can think of two scenarios that could explain your issue:

Your C# project is set to target both 64-bit and 32-bit platforms and the 64-bit machine has only the 32-bit version of the Access Database Engine installed. The reference fails because the program is running as 64-bit but the machine is missingC:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLLYour C# project is set to target 32-bit platforms only (x86) and the 64-bit machine has only the 64-bit version of the Access Database Engine installed. The reference fails because the program is running as 32-bit but the machine is missingC:\Program Files (x86)\Common Files\microsoft shared\OFFICE14\ACEOLEDB.DLL

Note that the installer for the 64-bit version of the Access Database Engine installs just the 64-bit version, not both.

It made me look into where my driver was installed. My server is 64 bit server and my code expected OLEDB driver(ACEOLEDB.DLL) to be in  C:\Program Files\Common Files\microsoft shared\OFFICE14\ACEOLEDB.DLL, but the dll was actually present in C:\Program Files (x86)\Common Files\microsoft shared\OFFICE14\ACEOLEDB.DLL

Finally found the solution: 

http://www.connectionstrings.com/the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/

On 64-bit Windows and 64-bit Office (2010, 2013) environments, there are many reports on this error. The fix or workaround is a bit strange but seems to work for most people out there.
The "Microsoft Access Database Engine 2010 Redistributable" installation package seems the natural one to use but several reports says it does not work.
Instead, using the "2007 Office System Driver: Data Connectivity Components" seems to solve the above problem for most people.

And as suggested by many people, the final step was by changing the Visual Studio: Build | Platform Target explicitly to X86. I had it set to Any CPU previously.

MySql Installation

Prerequisites
--------------

1. Microsoft .NET Framework 4 Client Profile
2. Microsoft Visual C++ 2010 Redistributable Package (x86)

Install

--------------

1. mysql-5.5.25-winx64.msi

2. mysql-connector-net-6.5.4.msi

3. mysql-workbench-gpl-5.2.40-win32.msi

These are the 3 files with which my sql set up comes .
Install all the 3 files and run the work bench.

While installing it will ask for password give as "root"
for the username root.

Registering the ASP.Net with IIS server on new server

1. Install 4.5 framework 

2. Get IIS feature enabled 

3. Open inetmgr - check if "ISAPI filter and CGI restrictions" is visible, if not :

a.       32 BIT: C:\Windows\Microsoft.NET\framework

b.      64 BIT: C:\Windows\Microsoft.NET\Framework64

4. Pick both the 2.0 and 4.0 versions and open cmd in admin mode

5. CD C:\Windows\Microsoft.NET\Framework64\v4.0.30319\

5. aspnet_regiis -i

6. CD C:\Windows\Microsoft.NET\Framework64\v2.0.50727\

7. aspnet_regiis –i

8. Once the ASP.Net is installed in IIS, you will see ASP.Net 4.0 Application pools in the IIS server

10. Now hosting a web service post upgration of .net framework version and browsing on a browser give the following error:

HTTP Error 404.17 - Not Found

The requested content appears to be script and will not be served by the static file handler.

11. Now the ASAPI filter and CGI Restrictions option will be visible.

12. Change the restriction level to Allowed for Framework 4.0

CREDITS: aspsnippets.com and other web sites which I refered.