Excel Oledb settings

1. Value of the IMEX parameter in the connection string:
0 is Export mode 1 is Import mode 2 is Linked mode (full update capabilities)
IMEX has three values: When IMEX = 2, an EXCEL file contains both numeric and numeric types. For example, column C has three values, two are numeric type, and one is numeric type ABC. When importing,
No error is reported on the page, but the Library only displays 123 of the numeric type, while ABC of the numeric type is displayed as null. When IMEX = 1, none of the above conditions occur, the Library can correctly present 123 and ABC.
2. Parameters HDR value:
HDR = Yes, which indicates that the first line is the title and is not used as data. If HDR = NO is used, it indicates that the first line is not the title and used as data. The default value is YES.

 

Note:

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 mixed 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 ISAM.
The driver samples the previous data items to determine the data type. The default value is "8 ". You can modify "HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft
The registry value under \ Jet \ 4.0 \ Engines \ Excel to change the number of samples. However, this improvement still does not fundamentally solve the problem, even if we set IMEX to "1 ",
TypeGuessRows is set to be larger. For example, if the data table contains 1000 rows and the first 1001 rows of a column are all pure numbers, the first 1000 rows of the column is a text, and ISAM drives
The Dynamic Mechanism still makes the data in this column empty.