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

Source: Internet
Author: User
Tags ole

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.