Format of OLE DB connection string for Excel (Provider=microsoft.ace.oledb)

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)