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: Error “External table is not in the expected format” using Excel 2007 workbook
Viewed 4239 times since Wed, Oct 14, 2009
CR: SQL Server Stored Procedures Returns No Records
Viewed 2349 times since Fri, Nov 9, 2007
CR: Excel tips
Viewed 3125 times since Tue, Oct 6, 2009
CR: Multiple chart lines from one data column
Viewed 2357 times since Fri, Nov 9, 2007
CR: Changing the chart type will change the Default Chart Style to missing
Viewed 2214 times since Fri, Nov 9, 2007
CR: A custom query for Length of Stay data
Viewed 2657 times since Fri, Nov 9, 2007
CR: Time Display
Viewed 2156 times since Fri, Nov 9, 2007
CR: Too many decimal places in the chart data grid for an Excel column that is calculated by a formula
Viewed 3925 times since Fri, Nov 9, 2007
CR: Making an X-bar chart with a single data column
Viewed 2160 times since Fri, Nov 9, 2007
CR: Formatting an Excel Date
Viewed 2625 times since Fri, Nov 9, 2007