When importing a mix of numbers, letters, and columns in an Excel file and importing letters into a null value, you can use the following methods to solve the problem!

When Microsoft. Jet. oledb.4.0 is used to read data, a type of data may be lost when a field contains both text and numbers.

The root cause of this problem is the Excel isam [3] (indexed sequential access method, I .e. the index sequential access method) driver.ProgramThe Excel isam driver checks the actual values in the first few rows to determine the type of an Excel column, and then selects the data type that can represent a large score in its sample [4]. That is, Excel isam searches for the first few rows of a column (eight rows by default) and takes the most types as the processing type. For example, if the number is large, other data items containing letters and other texts are left blank. If the number is large, data items with only numbers are left blank.
The specific analysis procedure is described in Section 1st.CodeDescription of HDR and IMEX in extended properties. HDR is used to set whether to use the first row of the Excel table as the field name. "Yes" indicates that "no" indicates that it is not the data content; IMEX is used to tell the driver to use the Excel file mode. Its values include 0, 1, and 2, which respectively represent the export, import, and hybrid modes.

When we set IMEX = 1, the forced hybrid data is converted to text, but this setting is not reliable, IMEX = 1 only works when at least one of the first eight rows of data in a column is a text item, it only slightly changes the behavior of selecting the dominant data type in the first eight rows of data. For example, if the first eight rows of data in a column are all pure numbers, the Data Type of the column is still numeric, and the text data in the row is still null.
Another improvement measure is that IMEX = 1 is used with the registry value typeguessrows. The value of typeguessrows determines the Data Type of the isam driver from the previous data sampling. The default value is "8 ". You can change the number of sampled rows by modifying the registry value under "HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ jet \ 4.0 \ engines \ excel. However, this improvement still does not fundamentally solve the problem. Even if we set IMEX to "1" and typeguessrows to be larger, for example, 1000, suppose the data table has 1001 rows, the first 1000 rows of a column are all pure numbers, and the first 1,001st rows of the column are a text. The isam-driven mechanism still makes the column data empty.

 

If it still does not work, select the full text "set cell format" as the text format, and the test is successful.