Myths Around MS-Excel As Data Source Using .Net (C#)

In this blog you will learn about myths around MS-Excel as Data source using .Net (C#).

Recently, I observed people using MS-Excel as a data source using a couple of projects,

1. Interop
   (Without knowing the pre-requisites in the production environment; i.e., MS-Office is not allowed in a production environment)
   
   
2. 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.



Excel Behavior

This is the default behavior of Excel. It decides the column datatype based on the first eight rows.

Alternative Approach

1. Read data using Excel –cell range
2. Third party open source tool has a  supported framework; i.e., 4.6 onwards.

Best Approach (Recommended)

1. Always use CSV file instead of Excel. It's easy to traverse using file stream objects.
2. 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.