This article mainly introduces the detailed explanation of HDR and IMEX parameters in the Excel connection string. These two parameters will be used when importing and exporting Excel data. Friends in need can refer to it.
Value of parameter HDR:
HDR=Yes, which means the first line is the title and is not used as data. If HDR=NO is used, it means the first line is not the title and is used as data.
The system default is YES
ParameterExcel 8.0
For Excel 97 and above, use Excel 8.0
IMEX (IMport EXport mode) settings
IMEX has three modes:
Copy the code code as follows:
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)
What I particularly want to explain here is the IMEX parameter, because different modes represent different reading and writing behaviors:
Copy the code code as follows:
When IMEX=0, it is "export mode". The Excel file opened in this mode can only be used for "writing" purposes.
When IMEX=1, it is "Import mode". The Excel file opened in this mode can only be used for "reading" purposes.
When IMEX=2, it is "link mode". The Excel file opened in this mode can support both "reading" and "writing" purposes.
The meaning is as follows:
Copy the code code as follows:
0 —Output mode;
1—Input mode;
2—Linked mode (full update capability)
Copy the code code as follows:
connstr = "Provider=Microsoft.ACE.OLEDB.12.0;
Persist Security Info=False;Data Source="&database&";
Extended Properties=Excel 8.0;HDR=Yes;IMEX=2";
In this case, "No installable ISAM found" may appear.
Most of the reasons why this problem occurs is because there are some missing elements in our sentences.
Copy the code code as follows:
connstr = "Provider=Microsoft.ACE.OLEDB.12.0;
Persist Security Info=False;Data Source="&database&";
Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'";
Copy the code code as follows:
connstr = "Provider=Microsoft.ACE.OLEDB.12.0;
Persist Security Info=False;Data Source="&database&";
Extended Properties=/"Excel 8.0;HDR=Yes;IMEX=2/"";
Both of the above situations can be solved.