Home » Categories » CHARTrunner

CR: Some Excel Spreadsheet Values Seem To Be Missing

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 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: Multiple chart lines from one data column
Viewed 2290 times since Fri, Nov 9, 2007
CR: Why is my first row of data not included when I get my chart from Excel
Viewed 2146 times since Fri, Nov 9, 2007
CR: Time Display
Viewed 2107 times since Fri, Nov 9, 2007
CR: Bar Chart of a column of numbers in various categories
Viewed 1999 times since Fri, Nov 9, 2007
CR: CR1.1 - Notes and Causes Problem
Viewed 2115 times since Fri, Nov 9, 2007
CR: Excel tips
Viewed 3067 times since Tue, Oct 6, 2009
CR: The out-of-control (OOC) test may fail on the last sample with valid data
Viewed 2115 times since Fri, Nov 9, 2007
CR: Unable to fully display settings and windows
Viewed 1970 times since Fri, Jan 22, 2010
CR: SQL Server Stored Procedures Returns No Records
Viewed 2299 times since Fri, Nov 9, 2007
CR: A custom query for Length of Stay data
Viewed 2581 times since Fri, Nov 9, 2007