The imported Excel value cannot be read, and the installable ISAM error cannot be found!

Recently, I was working on the function of importing Excel Data to the corresponding fields in the database table. At the beginning, there was no difficulty and the process was still smooth. However, during the next test, however, I encountered some very difficult problems, such as crazy experiments and non-stop searching on the Internet. Many people also had the same problem, but none of them solved the problem clearly, now that you have solved the problem yourself, let's take a look at the process and reasons for solving the problem and share it with you.
The idea of importing an EXCEL file is to import some basic data from the EXCEL file to initialize some basic data in the system, each table in these basic data tables is associated with several different tables, that is, to ensure the effectiveness of the data, and to import the correlation, it is really hard for us. if you do not need to import the program, how can you import the basic data? If you want to import the basic data, use the SQL method.
First, no matter what the data in the EXCEL table is, create a temporary physical table and import the data in the EXCEL; then, use SQL statements to develop SQL statements for the temporary table and related tables. This is indeed simple and flexible. In the future, when the customer has other abnormal requirements, it is too big to write a stored procedure for him, if it is more complicated, it can be more complicated. It's really nice to solve all the problems.
After the test, several difficult problems are found.
Problem: data-driven problem. Some columns in Excel are numerical values. Although this column is changed to text storage or other methods, in this way, the data in these columns cannot be read (string strCon = @ "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ Path +"; Extended Properties = Excel 8.0; ";), strange. I tried to modify the EXCEL storage mode, format, and then modify the connection string. Finally, I found that there was a problem with the data driver. I changed it to string strCon = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ Path +"; Extended Properties = 'excel 8.0; HDR = NO; IMEX = 1' "; that's right, here you need to pay attention to a problem 'excel 8.0; HDR = NO; IMEX = 1', with Excel 8.0; HDR = NO; IMEX = 1 difference, without the 'number will always be unable to find the installable ISAM error!
Parameter description:

The Microsoft Jet provider is used to connect to an Excel Workbook. Among the following connection strings, Extended Properties is critical

Set Excel attributes. "HDR = Yes;" indicates that the first line contains the column name, not the data. "IMEX = 1;" notifies the driver

Data columns are always read as text. Note:

Http://msdn2.microsoft.com/zh-cn/library/ms254978.aspx
By default, the system determines that the first row of the Excel Data Source contains the column title that can be used as the field name. If this is not the case, you must

Disable this setting. Otherwise, the first line of data will disappear and be used as the field name. This can be done by extending attributes to the connection string

Add optional HDR = settings. By default (not required), HDR = Yes. If no column title exists, you must specify

HDR = No; the provider name the field F1, F2, and so on. Because the extended attribute string currently contains multiple values, quotation marks must be used.

Pack it separately, as shown in the following example (additional spaces are added for easy visibility ).

For example, if 1st medium hdr = no, an error will be reported during where.

ConnStr = "Driver = {Microsoft Excel Driver (*. xls)}; Dbq = C: \ Inetpub \ wwwroot \ test.xls;

Extended Properties = 'excel 8.0; HDR = No; IMEX = 1 '"

Extended Properties parameter Properties write Excel 5.0; HDR = YES; IMEX = 1
IMEX = 1 refers to hybrid conversion to text
Select * from
OPENROWSET ('Microsoft. JET. OLEDB.4.0 ′
, 'Excel 5.0; HDR = YES; IMEX = 2; DATABASE = c: \ book1.xls ', [sheet1 $])

1) When hdr = yes, you can view the 1st rows of xls as fields. For example, if hdr = no is set to 1st, an error will be reported during where.
2) [] and Meiyuan $ are required; otherwise, M $ may not recognize this account.

I hope it will be helpful for anyone who can import EXCEL!

 

Reference from http://www.cnblogs.com/mczhu/archive/2009/01/28/1244978.html