Revision Date: 2005-09-19
Problem:
| When CHARTrunner fetches a column of data from an Excel spreadsheet and the column contains both numeric and non-numeric data then in some cases not all of the data will appear on the chart.
Cause:
By default the Jet 4.0 database engine, which is used to read Excel spreadsheets, examines the first 8 rows of a column to determine the data type. If it guesses Text, then any subsequent rows containing numbers are fetched as Null. If it guesses Numeric, then any rows containing non-numeric data are fetched as Null. CHARTrunner displays these Null values as blank or missing data. | |
Solution:
| CHARTrunner 1.6 contains a fix for this issue. Use the attached ExcelExamineAllRowsForMixedType.reg to modify the registry as explained below in the Technical Details section.
Download ExcelExamineAllRowsForMixedType.reg to a folder on your local disk and double-click the file to enter the changes into your registry. This file may also be found in the \Support\ChartRunner folder on the Quality Suite CD.
Technical Details:
CHARTrunner 1.6 forces the Jet database engine to honor the "ImportMixedTypes" registry setting in HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes.
It may be necessary for the user to modify the registry value HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows which accepts a decimal value from 0 to 16. Setting this to zero results in Jet examining ALL the rows of the column to determine whether it is dealing with a column containing a "mixed" data type and thus "ImportMixedTypes" applies. If this is set to 8 (the default) and the first 8 rows of data contain a text value, then Jet doesn't assume the column contains "mixed" data types and any rows containing a pure number will be Null since a number is not a Text value.
This fix does not apply to using an ADO data provider with Excel, although it is unlikely anyone would use ADO to get to Excel data.
See Microsoft knowledgebase articles 257819 and 194124 for more technical details. | |
