String Strcon = "Provider=microsoft.ace.oledb.12.0;data source=" + FilePath + "; Extended properties= ' Excel 12.0; Hdr=yes;imex=1 ' ";
Excel 2007 File
Provider=microsoft. jet.oledb.4.0; Data source= "+ FilePath +"; Extended properties= ' Excel 8.0; Hdr=yes;imex=1 ' ";
Excel 2003 File
The Jet engine can access office97-2003, but cannot access Office 2007.
The ACE engine, a database connectivity component that is published with Office2007, can access both Office 2007 and office97-2003.
Additionally: microsoft.ace.oledb.12.0 can access the Excel file that is being opened, and microsoft.jet.oledb.4.0 is not possible.
HDR Indicates whether the first row is a header row.
- If yes, the first row is the header row (that is, the column name), not the data;
- If no, the first row is not the header row, as in the following row, is the data.
IMEX represents how a mixed data type is handled.
- If 0, then the output mode, in this case can only be used to write to Excel;
- If 1, the input mode, in which case it can only be used to read Excel and always read the Excel data as a text type;
- If 2, the connection mode, in which case it can be used as a write or as a read.
The first column of Excel can store strings, the first column in the second row can store numbers ... The same columns, which store different data types, form a mixed data type.
Therefore, to read a mixed data type, the IMEX should be set to 1, if the error is set to 0, no rows will be read, and if the error is set to 2 or omitted, some data read out is blank.
PS: Imex=1 only works by ensuring that at least one of the first 8 rows of data in a column is a text item, it simply changes the behavior of selecting the preferred data type 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.
Format of OLE DB connection string for Excel (Provider=microsoft.ace.oledb)