The correct connection string should be similar to the one shown below: This is very important since excel will still determine the data type based on sampling (considering the first 8 rows), and it determines the data type based on the majority number of types. HDR=NO Option: This option will inform the OLEDB engine not to consider the first row as the header row. In our case, we need to set it up as 1, which means during import all the data is to be considered as text type. IMEX=1 Option: There are other types which can be used and each denotes a different option. Secondly, we need to consider switching the HDR=NO in the connection string (or set FirstRowHasColumnNames to False) ![]() In order to overcome this issue, we need to do a few things.įirstly we need to include the parameter "IMEX=1" in the connection string (or in the extended properties.) To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window You can modify this behaviour of the Excel driver by specifying Import Mode. ![]() (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favour of the majority data type and returns null values for cells that contain data of the other type. ![]() Root Cause (As per Microsoft explanation) The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |