Home » Categories » CHARTrunner

CR: Some Excel Spreadsheet Values Seem To Be Missing

Revision Date: 2005-09-19


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.


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.


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 SupportChartRunner folder on the Quality Suite CD.

Technical Details:

CHARTrunner 1.6 forces the Jet database engine to honor the "ImportMixedTypes" registry setting in HKLMSoftwareMicrosoftJet4.0EnginesExcelImportMixedTypes

It may be necessary for the user to modify the registry value HKLMSoftwareMicrosoftJet4.0EnginesExcelTypeGuessRows 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.

Attachments (1) Attachments
Related Articles RSS Feed
CR: CHARTrunner and SharePoint
Viewed 3899 times since Fri, Mar 9, 2012
CR: Time Display
Viewed 2038 times since Fri, Nov 9, 2007
CR: Histogram bars beyond specification lines
Viewed 2267 times since Fri, Nov 9, 2007
CR: Multiple chart lines from one data column
Viewed 2180 times since Fri, Nov 9, 2007
CR: Why is my first row of data not included when I get my chart from Excel
Viewed 2076 times since Fri, Nov 9, 2007
CR: Formatting an Excel Date
Viewed 2399 times since Fri, Nov 9, 2007
Cr: Charting elapsed time
Viewed 2399 times since Fri, Nov 9, 2007
CR: A custom query for Length of Stay data
Viewed 2450 times since Fri, Nov 9, 2007
CR: Show every date on the chart
Viewed 2189 times since Thu, Jan 31, 2008
CR: Charts with large amounts of data
Viewed 1870 times since Fri, Nov 9, 2007