Myth around MS-Excel as Data source using .Net (C#)
Recently, I observed in the couple of projects, people use MS-Excel as data source using
1. Interop – (Without knowing the pre-requisites in production environment i.e. MS-Office not allowed in production environment)
2. Third party tool (free & open source) – Use unsupported.Net Framework.
This article emphasis, known issue to use MS-Excel as data source using .Net (C#).
Problem Statement
Excel column decide the datatype based on a certain number of rows i.e. 8. If we are using excel with mixed datatype i.e. alphanumeric value where First 8 as the number and 9 as a string -> which return null as below source.
Excel Behavior
This is the default behavior of excel. It decides the column datatype based on first 8 rows.
Alternative Approach
1. Read data using Excel –cell range
2. Third party open source tool having supported framework i.e. 4.6 onwards.
Best Approach (Recommended)
1. Always use CSV file instead of Excel. Easy to traverse using file stream object.
2. Registry Changes - If business allowed.
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 .xlsx
Registry Key Excel 2010:
|
HKEY_LOCAL_MACHINE\Software\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows
|
Excel file is .xlsx
Registry Key Excel 2013:
|
HKEY_LOCAL_MACHINE\Software\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows
|
Note: - Value should be 0 instead of 8.