Controlling how MapInfo Pro Handles Mixed Data Type Columns - MapInfo_Pro - 2023

When working with an Excel 2010 table in MapInfo Pro if there are mixed types of data in one column, such as float, text, date, and time, then MapInfo Pro treats all the data in the column as text. If MapInfo Pro treats the data type of the column as something other than text, then you can correct this by updating a Microsoft ACE (Access Connectivity Engine) registry key.

MapInfo Pro detects when an Excel 2010 table has a column with mixed data types, because of the ACE setting in the registry. This registry key is called ImportMixedTypes and is located in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel. It decides how to import mixed data and by default it is set to a text value. A secondary registry key called TypeGuessRows determines how many rows to scan for each column before the ACE decides that a column has mixed data types. A value of zero (0) scans all the rows to determine if a column has mixed data types; the default setting is eight (8) to scan eight rows.

To ensure that the data types of all columns are detected correctly, change the TypeGuessRows key in the registry to zero (0). Note that scanning all of the rows in a large spreadsheet can be slow. To edit the registry, you will need Administrator privileges. If you do not have the required privileges, then ask your System Administrator to assist you.

Before modifying the TypeGuessRows key in the registry, make a backup:

1. Close any programs that are running.
2. On the Start menu, in the Search programs and files field, type regedit. Click OK to display the Registry Editor window.
3. In the HKEY_LOCAL_MACHINE entry, navigate to Software > Microsoft > Office > 14.0 > Access Connectivity Engine > Engines > Excel.
4. Highlight the Excel folder and then on the File menu, click Export.
5. In the Export Registry File dialog, specify a location and file name to backup to and then click Save.
6. Continue to the next set of instructions to set the TypeGuessRows key.

To set the TypeGuessRows key:

1. Select TypeGuessRows and on the Edit menu click Modify to display the Edit DWORD Value dialog box.
2. In the Value data field, type 0 (zero) and click OK.

This option takes effect the next time you open MapInfo Pro.