1865522 - Text truncated when reporting off a MS Excel spreadsheet in Crystal Reports

SAP Knowledge Base Article - Public

1865522 - Text truncated when reporting off a MS Excel spreadsheet in Crystal Reports

Symptom

  • Text truncated.
  • When refreshing a report off a MS Excel spreadsheet in Crystal Reports, the text is truncated at 255 characters.

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

Reproducing the Issue

  1. In Crystal Reports, create a report off a MS Excel spreadsheet that contains at least one column with large amount of text.
  2. Add a couple of database fields, including one database field that contains large number of characters.
  3. When refreshing the report, notice the text is truncated.

Cause

  • This situation occurs because Microsoft guess the data type of the MS Excel spreadsheet columns based on the first 8 rows of data. If the text is not longer than 255 characters in the first 8 rows, it then determine the data type is a string of less or equal to 255 characters, and this is why it truncates the data at 255 characters because this is the information Microsoft send to Crystal Reports.
  • For reference, see the Microsoft Knowledge Base Article: 189897

Resolution

  • To change how Microsoft determine the data type of each column, set the Microsoft registry key: TypeGuessRows, to no longer guess the data type by setting the value to: 0 (zero). 

    ***WARNING***: The following resolution involves editing the registry. Using the Registry Editor incorrectly can cause serious problems. Use the Registry Editor at your own risk. Refer to the SAP Knowledge Base Article 1323322 for more information.

    1. Open the Microsoft Registry Editor ( regedit.exe )
    2. Go to the path that correspond to the type of connection, version of MS Excel and version of MS Windows:
          
      • For a native connection or an OLE DB connection using the Microsoft Jet 4.0 OLE DB Provider:
            
        • With MS Excel 2003 to MS Excel 2013:
              
          • For 32bit version of MS Windows
            - HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
                  
          • For 64bit version of MS Windows:
            - HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

                   
      • For an ODBC connection or an OLE DB connection using the Microsoft Office Access Database Engine OLE DB Provider:
           
        • With MS Excel 2007:
            
          • For 32bit version of MS Windows
            - HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel
               
          • For 64bit version of MS Windows:
            - HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel
                
        • With MS Excel 2010:
             
          • For 32bit version of MS Windows
            - HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel
                
          • For 64bit version of MS Windows:
            - HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel
               
        • With MS Excel 2013:
             
          • For 32bit version of MS Windows
            - HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel
                
          • For 64bit version of MS Windows:
            - HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel
             
              
    3. Set the registry key: TypeGuessRows
      To the value: 0

Now, when creating a new report off the MS Excel spreadsheet in Crystal Reports, it will no longer truncate the long text.
 

See Also

1942272 - Incorrect data type when reporting off a MS Excel spreadsheet in Crystal Reports.

Keywords

CR, xls, excel , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016