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: Installing CHARTrunner disables desktop wallpaper
Viewed 2059 times since Fri, Nov 9, 2007
CR: Label your X-Axis with more than one identifier
Viewed 2522 times since Tue, Oct 6, 2009
CR: Charting pass-fail data as a p-chart
Viewed 4450 times since Fri, Feb 29, 2008
CR: Chart [the name of the chart] failed with one or more errors
Viewed 2068 times since Fri, Nov 9, 2007
CR: Data grouping tardiness
Viewed 3161 times since Tue, Oct 6, 2009
CR: Charting counted incidents
Viewed 1935 times since Thu, Feb 23, 2012
CR: Grouping counts by date in Excel
Viewed 2170 times since Fri, Nov 9, 2007
CR: SQL Server Stored Procedures Returns No Records
Viewed 2269 times since Fri, Nov 9, 2007
CR: Error “External table is not in the expected format” using Excel 2007 workbook
Viewed 4036 times since Wed, Oct 14, 2009
CR: Largest Subgroup Size Supported by CHARTrunner
Viewed 2025 times since Fri, Nov 9, 2007