Data truncated to 255 characters with Excel ODBC driver

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q189897
This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
When you retrieve external data using the Microsoft Excel 97 ODBC driver,fields that have more than 255 characters may be truncated to 255characters.
CAUSE
Your data may be truncated to 255 characters if the first 8 records for thefield(s) being truncated contain 255 or fewer characters. The MicrosoftExcel ODBC driver will, by default, scan the first 8 rows of your data todetermine the type of data in each column.

Even though you can change the Rows To Scan value in the ODBC MicrosoftExcel 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 theTypeGuessRows DWORD value of one of the following registry key to determine howmany rows to scan in your data:
  • Excel 97
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel
  • Excel 2000 and later versions
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
RESOLUTION
To change the number of rows that the Excel ODBC driver scans to determinewhat type of data you have in your table, change the setting of theTypeGuessRows DWORD value.

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

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
322756 How to back up and restore the registry in Windows


For information about how to edit the registry, view the "Changing Keys AndValues" Help topic in Registry Editor (Regedit.exe) or the "Add and DeleteInformation in the Registry" and "Edit Registry Data" Help topics inRegedt32.exe. Note that you should back up the registry before you edit it.If you are running Windows NT, you should also update your EmergencyRepair 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 and click OK.
  3. In the Registry Editor, expand the following key depending on the version of Excel that you are running:
    • Excel 97
      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel
    • Excel 2000 and later versions
      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\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 isset to zero, Microsoft Excel will scan all records in your table todetermine the type of data in each column.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
XL97 query XL2000 XL2002 XL2003
Properties

Article ID: 189897 - Last Review: 12/05/2015 08:59:23 - Revision: 2.2

Microsoft Office Excel 2003, Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition, Microsoft Excel 97 Standard Edition

  • kbnosurvey kbarchive kbbug kbpending KB189897
Feedback