Excel connection string

When reading an Excel file through oledb, some fields are often read as null values. In fact, there is a value, because when reading a file, Excel usually uses the data type of the previous 10 rows as a reference, if they are different, some problems may occur. You can modify the connection string of an Excel file to forcibly read data of the string type.

String xlsconnformat = @ "provider = Microsoft. jet. oledb.4.0; Data Source = '{0}'; extended properties = 'excel 8.0; HDR = no; IMEX = 1 ';"; // 1. HDR indicates whether to use the first row as the data or column name, HDR = No as the data, and HDR = yes as the column name; // 2. Use IMEX = 1 to read the hybrid data as the text type to avoid null values.

Note: consider an Excel file as a database and a sheet as a table. Syntax "select * from [sheet1 $]", the form must use "[]" and "$"

Default Excel table, column name is "F1 ~ F99 ".

If the message "unable to find the installable isam" is displayed ." The error is mainly caused by the connection string. check whether there are spaces between data sources and whether extended properties are correctly written.

Important Note: If you are selecting data for a specific date you can use the following:
"Select * from [sheet1 $] Where datecol = #3/15/2009 #"
Don't try to use the single quotes "'".