I'm using the below connection string with ace.oledb.12.0 to read data from an xlsx spreadsheet, but the i set imex=1, it does not work while when i remove imex=1 completely, it works fine 3 per the article below, imex values of 0 and 2 do the same thing That is the default if you don't specify imex=1 in your extended properties So the default imex behavior is majoritytype, and this can be overridden by using imex=1 in your connection string along with the registry setting. You can add imex=1 to the dynamic expression connectionstring, but this is not useful at design time when setting up the column datatypes One workaround is to add string data to at least 5 of the top 8 rows for the design time excel file (so when ace guesses the column datatype it chooses string even without imex=1).
Oledb not reading all rows from excel file, in spite of using imex=1 asked 12 years, 9 months ago modified 12 years, 8 months ago viewed 5k times (a caution) as mentioned above, excel will have to guess a number or rows to select the most appropriate data type of the column, a serious problem may occur if you have mixed data in one column Say you have data of both integer and text on a single column, in that case, excel will choose its data type based on majority of the. I use adodb to pull data from excel file to another excel file without opening the source I have a strong suspicion that the imex=1 parameter is not accepted in my connection I'm trying to import an excel file to vb studio 2005 using imex=1
To work around this problem for data, set imex=1 in the extended properties section of the connection string This enforces the importmixedtypes=text registry setting. Imex=1 means that when the driver encounters mixed types in the first 8 rows that it should treat the column as text Without it it will scan the first 8 rows, determine the data type and return null for those cells that do not correspond to the data type.
OPEN
