C # Resolves a solution that reads a mixed text type of Excel and fails to read data

Error Recurrence:

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

When you import Excel to read data, one of the fields holds the following format: "2011072014", "20110aad10", "25124Adfa", "example ASDFADF", such as mixed with "letter/number/Chinese" data, in the Excel table before The data in this column in 8 or 8 + Bar data is made up of pure numbers, such as "2011072012", "2011072013", "2011072014", and the data composition of the column becomes a combination of numbers + Chinese + letters when it is greater than 8 rows. This will cause an issue where the column value cannot be taken

Cause of Error:

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

Excel reads data columns in a format that is based on the type of first 8 data in each column to determine the type of read-write. For example, 3 of the first 8 data are subtitles, 5 is a number, then this whole column is a digital type to read, the type is the most then regardless of the type after 8th, all as this type to read, those letters are not read.

Reading data using microsoft.jet.oledb.4.0 will result in the loss of data of a certain type when mixed data containing text and numbers is contained within a field.

The source of this problem is related to the limitations of Excel Isam[3] (Indexed sequential access method, the indexed sequential access methods) driver, and the Excel ISAM driver determines the type of an Excel column by examining the actual value in the first few rows , and select the data type that represents most of the values in its sample [4]. That is, the Excel ISAM finds the first few rows of a column (8 rows by default), and takes as many types as its processing type. For example, if the number is more, then other items containing text such as letters will be empty, whereas if the text is mostly, the data items of the pure numbers will be empty.
The meaning of HDR and IMEX represented in the 1th section of the program code extended Properties item is now analyzed concretely. HDR is used to set whether the first row in the Excel table is the field name, "Yes" means yes, "no" means the data content; Imex is the mode used to tell the driver to use the Excel file, with values of 0, 1, and 23 representing the export, import, and blending modes respectively.

When we set Imex=1 to force mixed data to be converted to text, but this setting is not reliable, imex=1 only makes sure that the first 8 rows of data in a column are at least one of the text items, only to make a slight change in the behavior of finding the best selection of data types in the first 8 rows of data. For example, the first 8 rows of data in a column are all pure numbers, so it still has a numeric type as the data type of the column, and then the data containing the text in the row remains empty.
Another improvement is that Imex=1 is used with registry value TypeGuessRows, and the TypeGuessRows value determines the data type determined by the ISAM driver from several previous data samples, which defaults to "8". You can change the number of sample rows by modifying the registry value under Hkey_local_machine\software\microsoft\jet\4.0\engines\excel. However, this improvement does not fundamentally solve the problem, even if we set the Imex to "1", typeguessrows set to a larger, such as 1000, assuming that the data table has 1001 rows, a column of the first 1000 rows are all pure numbers, the column of the 1001th row is a text, The ISAM-driven mechanism still makes this column of data empty.

In SQL Server usage

SELECT *
From OpenDataSource (' microsoft.jet.oledb.4.0 ',
' Data source= ' e:\. xls ";
User id=admin; password=; Extended properties= ' Excel 8.0; Hdr=yes;imex=1 ")

Add the following code to the project to automatically modify the registry! RegistryKey reg_typeguessrows = Registry.LocalMachine.CreateSubKey (@ "software\microsoft\jet\4.0\engines\ Excel"); Reg_typeguessrows.setvalue ("typeguessrows);      

C # Resolves a solution that reads a mixed text type of Excel and fails to read data