How JET( OLE DB) decide the Excel Column type ?

Few days back, I was working on a tool( this tool would be using in the other countries as well) which was using Excel as a Data Source and I was using OLE DB( JET ) to communicate with the Excel.

However, an interesting issue came up while using the tool in China, whenever somebody enters Chinese char thru the tool gets the ever helping error message “Multi Step generated Error, check each status value”.

While investigating the issue, it came up that the Column type is the culprit, JET was setting the column type as ADVARCHAR, and to assign a Chinese char( read UNICODE ) the column type has to be ADVARWCHAR, but the question here is how to set column type in Excel ? so how does JET decide a Column type ?

What JET do is…it scans a number of rows( default is 8, goes by the value set in Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ TypeGuessRows ) for each column and decide the column type, for example a column has 8 rows with numeric data, JET will set the data type as numeric, but consider a case where a column has 3 rows with numeric data and rest 5 with char data, what will be the type of the column ? JET will set the column type as ‘VARCHAR’.

Well, this is not all, there is one more registry key which also plays a very important role here, Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/ Excel/ImportMixedTypes … if the value of this key is ‘Majority Type’ then in the above case JET will set the column type as VARCHAR, however if the value of ‘ImportMixedTypes’ is ‘Text’ then JET will set the column type as UNICODE VARCHAR or ADVARWCHAR( irrespective of the column values)

However, as per JET documentation, we can override the registry setting thru the Connection String, if we set IMEX=1( as part of Extended Properties), the JET will set the all column type as UNICODE VARCHAR or ADVARWCHAR irrespective of ‘ImportMixedTypes’ key value.hey