XL97: Data Truncated to 255 Characters with Excel ODBC Driver

ID: Q189897

The information in this article applies to:

Microsoft Excel 97 for Windows

IMPORTANT: This article contains information about editing the registry. Before you edit the registry, make sure you understand how to restore it if a problem occurs. For information about how to do this, view the "Restoring the Registry" Help topic in Regedit.exe or the "Restoring a Registry Key" Help topic in Regedt32.exe.

SYMPTOMS

When you retrieve external data using the Microsoft Excel 97 ODBC driver, fields that have more than 255 characters may be truncated to 255 characters.

CAUSE

Your data may be truncated to 255 characters if the first 8 records for the field(s) being truncated contain 255 or fewer characters. The Microsoft Excel ODBC driver will, by default, scan the first 8 rows of your data to determine the type of data in each column.

Even though you can change the Rows To Scan value in the ODBC Microsoft Excel Setup dialog box to something higher than 8 (but not higher than 16) this value is not being used by Excel. The Excel ODBC driver uses the TypeGuessRows DWORD value of the following registry key to determine how many rows to scan in your data:

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel

RESOLUTION

To change the number of rows that the Excel ODBC driver scans to determine what type of data you have in your table, change the setting of the TypeGuessRows DWORD value.

NOTE: The following steps will only work if your source Excel file is saved in the Microsoft Excel Workbook file format. If it is saved in the Microsoft Excel 97 & 5.0/95 Workbook file format, the data will always be truncated to 255 characters.

WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the "Changing Keys And Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it. If you are running Windows NT, you should also update your Emergency Repair Disk (ERD).

To change the setting for the TypeGuessRows value, follow these steps:

1. Close any programs that are running.

2. On the Start menu, click Run. Type "regedit" (without the quotation

   marks) and click OK.

3. In the Registry Editor, expand the following key:

      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel

4. Select TypeGuessRows and on the Edit menu click Modify.

5. In the Edit DWORD Value dialog box, click Decimal under Base. Type a

   value between 0 and 16, inclusive, for Value data. Click OK and quit the
   Registry Editor.

NOTE: For performance reasons, setting the TypeGuessRows value to zero (0) is not recommended if your Excel table is very large. When this value is set to zero, Microsoft Excel will scan all records in your table to determine the type of data in each column.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel 97 for Windows.

Additional query words: XL97 query

Keywords          : kbdta xlquery xllist 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbbug
Solution Type     : kbpending

Last Reviewed: November 5, 1998