SAP Knowledge Base Article - Public

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

Symptom

  • Numbers displays as string.
  • Date displays as string.
  • Columns defined as number or date in a MS Excel spreadsheet, are defined as string data type in Crystal Reports.

Environment

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

Reproducing the Issue

  1. In a MS Excel spreadsheet, define a column data type as number or as date.
  2. Enter numeric values, or date in the columns.
  3. In Crystal Reports, create a report off the MS Excel spreadsheet.
  4. Notice the data type for numbers, or date in Crystal Reports is defined as string, which is incorrect.

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 there is no values in the first 8 rows, it then determine the data type is a string, and 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, the data type will reflect the data of the columns in the MS Excel spreadsheet.

Keywords

CR, incorrect data type, numeric, number, date , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

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