Recently, I observed people using MS-Excel as a data source using a couple of projects,
- Interop
(Without knowing the pre-requisites in the production environment; i.e., MS-Office is not allowed in a production environment)
- Third party
Tools (free & open source) – Use unsupported .Net Framework.
This article emphasizes known issues when using MS-Excel as a data source using .Net (C#).
Problem Statement
Excel columns decide the datatype based on a certain number of rows; i.e., eight. If we are using Excel with mixed datatypes; i.e., alphanumeric value where the first is eight as a number and nine as a string it will return null as below source.
![C#]()
Excel Behavior
This is the default behavior of Excel. It decides the column datatype based on the first eight rows.
Alternative Approach
- Read data using Excel –cell range
- Third party open source tool has a supported framework; i.e., 4.6 onwards.
Best Approach (Recommended)
- Always use CSV file instead of Excel. It's easy to traverse using file stream objects.
- Registry Changes - If business allows it.
| Excel file as .xls(Registry Key) | HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows |
Excel file is .xlsx
Registry Key Excel 2007: | HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows |
| Excel file is .xlsxRegistry Key Excel 2010: | HKEY_LOCAL_MACHINE\Software\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows |
| Excel file is .xlsxRegistry Key Excel 2013: | HKEY_LOCAL_MACHINE\Software\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows |
Note
Value should be 0 instead of eight.