Using C # To import access digital character mixing in an Excel file

How to use C # To import access numeric character mixing in an Excel file

The Excel driver reads a certain number of rows in the specified source (eight rows by default) to predict the Data Type of each column. If it is estimated that the column may contain the hybrid data type (especially when the text data is mixed), the driver will decide to use the dominant data type, return NULL values for units that contain other types of data. (If the number of data types is the same, the value type is used .)
The setting of most cell formats in an Excel worksheet does not affect the determination of this data type. You can modify this behavior of the Excel driver by specifying the import mode. To specify the import mode, add IMEX = 1 to the extended attribute value in the connection string of the Excel Connection Manager in the Properties window.

Therefore, if you do not specify IMEX = 1, the value of 72459473x read may be null, because most rows are pure numbers.

The following format can solve the problem:

String strconn = "provider = Microsoft. Jet. oledb.4.0;" // Excel is actually a data source.
+ "Data Source =" + filepath + ";"
+ "Extended properties = 'excel 8.0; HDR = yes; IMEX = 1 ';";

Note the 'sign after the properties property. Use the 'sign to enclose the three parameters.

 

The following question is: What do I mean by extended attributes such as IMEX = 1? The following two explanations should be clear enough:
1. IMEX = 1: tells the driver to always read "intermixed" Data columns as text
2. HDR = yes; to indicate that I have a header row in my sheets

Reference: http://www.cnblogs.com/soulstore/archive/2007/11/09/954460.html here to thank !!

Now the question is: What if the first 99 rows are numbers and the last row is a hybrid type?
When oledb was used to obtain Excel files, it was found that some of the values were valid, but the keyword was null. I checked it online, add extended properties to the explain strings and set IMEX to 1. Then, you can force the oledb driver to convert the content into text strings, in order to avoid the occurrence of non-linear errors and return null. as follows:

Provider = Microsoft. jet. oledb.4.0; Data Source = "C: \ data.xls"; extended properties = "Excel 8.0; HDR = yes; IMEX = 1; however, it is found that some information can be successful, some do not work, but if you change the sequence of the data column (put the non-numeric data in the comment column before the ratio ), I can use it again. It's really strange that I went to Google again and finally found the source of the problem... it turns out to be an oledb driver problem. On the machine, HKLM \ Software \ Microsoft \ jet \ 4.0 \ engines \ Excel has a typeguessrows value, which is set to 8, indicates that the first eight columns are used to determine the type of each vertex. Therefore, if the data in the first eight columns is a number, the text information displayed in the 9th column will change to null, which is so plain and ugly ..... so If you want to solve this problem, you just need to change the value of typeguessrows to 0 to solve this problem!

 

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ jet \ 4.0 \ engines \ Excel]
"Win32" = "C :\\ Program Files \ Microsoft Office \ office11 \ msaexp30.dll"
"Disabledextensions" = "! Xls"
"Importmixedtypes" = "text"
"Firstrowhasnames" = HEX: 01
"Appendblankrows" = DWORD: 00000001
"Typeguessrows" = DWORD: 00000000
"Win32old" = "C: \ WINDOWS \ system32 \ msexcl40.dll"

 

 

Reference:
Http://sanchen.blogspot.com/2007/08/imex1-oledb-excel-null.html

 

 

What does excel IMEX and HDR in oledb mean?

HDR = No: no field HDR = Yes indicates a field. Generally, the title of the column in the first row of the Excel table is the field name, such as name and age. Another problem is that IMEX has three values 0, 1, 2, and what the other two values represent respectively. IMEX indicates whether to forcibly convert to text Special note Extended properties = 'excel 8.0; HDR = yes; IMEX = 1' A:HDR (header row) settings If the value is yes, the first row of the worksheet in the Excel file is the column name. If the value is no, the first row of the worksheet in the Excel file is the item, and there is no column name. B: IMEX (Import Export mode) settings 　　There are three modes for IMEXAnd read/write Behaviors Different , And then describe: 0 is export Mode 1 is import Mode 2 is linked mode (full update capabilities) Here I particularly want to explain the IMEX parameter, because different modes represent different read/write behaviors: When IMEX = 0, the "Export mode" is enabled. The Excel files enabled in this mode can only be used for "writing" purposes. When IMEX = 1, it is set to "Import mode". The Excel files opened in this mode can only be used for "read" purposes. When IMEX = 2, the connection mode is enabled. The Excel files enabled in this mode support both "read" and "write.     Meaning: 0 --- output mode; 1 --- input mode; 2 ---- connection mode (full update capability)