Dealing with mixed data types in Excel ODBC queries (1)

There's a great post dealing with this at Daily Dose of Excel.  However, aware that I often don't have access to the registry on the machines I access, I was interested to find out if I could make queries work no matter what the registry contents*. 

The best situation to find yourself in is if your registry has an ImportMixedTypes value of Text.   This allows you to set IMEX=1 in the extended properties for the query.  If you set HDR=No as well, the text column headers that would be the field names if HDR=Yes, ensure that the query will return text values for every field.  Criteria can suppress the display of the record containing the column headers.  Data conversion calculations provide the output of the query in the appropriate data type on all records.  Using this methodology all the original data will be in field names F1, F2 etc.  The data conversion calculations performed in SQL can alias the field names to reflect the original field names.

Below is a select statement to demonstrate how to return each data type in the above scenario:
SELECT iif(isnumeric(F1), cdbl(F1)) as [Number],
iif(isnumeric(F2), ccur(F2),0) as [Currency], 
iif(isdate(F3),cdate(F3),#1900-01-01#) as [Date],
''+F4 as [Text],
iif(F5='TRUE',cbool(1),iif(F5='FALSE',cbool(0))) as [Boolean]

The following where clause ensures that the column header record is suppressed:
WHERE F4<>'Text' or isnull(F4)

In the above the currency column populates every record with a value, effectively converting nulls to zero.  Vital if any GROUP BY operations are to return a value other than null.  For comparison, the number column doesn't replace the nulls. Below is the QueryTable output:

Number	Currency	Date	Text	Boolean
	-	01/01/1900	Other fields are Text in the original data
	-	01/01/1900	Other fields are empty in the original data
1234	567.89	30/05/2011		-1
56	12.99	23/07/2011	The line above is empty	0

If you find that your ImportMixedTypes=Majority Type, then IMEX=1 has no practical effect.  If a mixed type field is determined to be a text type, all values of other types will be suppressed.  The only way to guarantee that the proper values are returned for all fields is to include at least half the number of rows specified by TypeGuess rows just below the column headers in the data.  These rows can be hidden once populated with the correct types of data.  This will ensure that the Majority Type identified is the correct type. With HDR=No, criteria can suppress the display of the record containing the column headers as well as the records contained in the now hidden data rows.  Data conversion calculations provide the output of the query in the appropriate data type on all records.  Using this methodology all the original data will be in field names F1, F2 etc.  The data conversion calculations performed in SQL can alias the field names to reflect the original field names.

Below is a select statement to demonstrate how to return each data type in the above scenario:
SELECT iif(isnumeric(F1), cdbl(F1)) as [Number],
iif(isnumeric(F2), ccur(F2),0) as [Currency], 
iif(isdate(F3),cdate(F3),#1900-01-01#) as [Date],
''+F4 as [Text],
iif(isnumeric(F5),cbool(F5)) as [Boolean]

The following where clause ensures that the column header record is suppressed in addition to the records contained in the now hidden data rows (populated with '0'):
WHERE F4 not in('Text','0') or isnull(F4) 
The QueryTable output is identical to that shown above.


* the one exception is if the registry has TypeGuessRows=0 (scan all rows) and ImportMixedTypes=Majority Type - an unlikely scenario.