Mixed Data Type in Excel

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.

Ref:- https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ce095b10-84a4-4ae3-8944-70a2b53daa44/mixed-data-types-in-excel-column-to-oedb-destination?forum=sqlintegrationservices