1451960 - Null or empty values are not surrounded with delimiter when exported to CSV format from Crystal Reports

SAP Knowledge Base Article - Public

1451960 - Null or empty values are not surrounded with delimiter when exported to CSV format from Crystal Reports

Symptom

  • Null or empty values are not surrounded by delimiter when exported to CSV format.
  • When exporting a report to CSV format from Crystal Reports, the database fields of type text that contains no data ( empty or NULL ) are not surrounded by the selected delimiter.

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
      
  • SAP Crystal Reports for Visual Studio 2010

Reproducing the Issue

  1. Create a report based off a table which contain a database fields that has null or empty values.
  2. Insert the database field in the details section of the report.
  3. Export the report to CSV format and add a delimiter. ( Example: use a double quotes as delimiter. )
  4. You will observe the null or empty fields will not be surrounded by delimiter when the csv file generated is open in a text editor.
     
    For Example:  If we have the following data on a report:

    Country    Address         Name
    Canada        NULL            John
    USA          First Street      Mark
    Mexico      <EMPTY>        Fernando
           
    When exporting the above report to CSV format and selecting the following option:
    Delimiter  : “
    Separator : ,
         
    It generate a csv file like:
        
    “Canada”,,”John”
    “USA”,”First Street”, “Mark”
    “Mexico”,,”Fernando”
           
    Notice the place where the value is NULL or empty, it doesn’t use the delimiter.
    The expected behavior will be to have the output like:
       
    “Canada”,””,”John”
    “USA”,”First Street”, “Mark”
    “Mexico”,””,”Fernando”

Cause

  • This is normal behavior, empty or null string will not be surrounded by a delimiter when exporting to CSV format.
  • An Enhancement Request has been logged under ID ADAPT01392393

Resolution

  • An enhancement was added in the following version of Crystal Reports to provide the option to add delimiters around NULL or empty values when exporting to CSV:
      
    • Crystal Reports 2008:
      • Fix Pack 2.8
      • Fix Pack 3.
      • Service Pack 4
           
    • Crystal Reports 2011:
      • Support Pack 01 and above
         
    • Crystal Reports 2013 and above
       
  • Note that by default, after applying the Fix Pack or Service Pack in which the enhancement was added, when exporting to CSV format, empty or NULL string will not be surrounded by a delimiter.
       
  • To have the selected delimiter exported to CSV format for NULL or empty string, add the registry key: DelimiterEmptyString, and set the value to: 1
      
    WARNING
    The following resolution involves editing the Microsoft Registry. Using the MS Registry Editor incorrectly can cause serious problems. Use the MS Registry Editor at your own risk. For more information see the SAP Knowledge Base Article 1323322 
       
    1. Close Crystal Reports designer.
          
    2. Open the Microsoft Registry Editor. 
      1. In MS windows, select "Run" under the menu "Start"
      2. Type: regedit
              
    3. In the MS Registry Editor, navigate to the path corresponding to the version of Crystal Reports used:
            
      • For Crystal Reports 2008:
              
            HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\Crystal Reports\Export\CSV  
              
      • For Crystal Reports 2011, 2013, 2016:
          
            HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Export\CSV

        Important Notes:
        - If the last part of the path does not exist, simply add the missing keys.
        - If you are using a 64bit Edition of MS Windows, the registry path will start with: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node    
             
    4. Under CSV, add the DWORD Value: DelimiterEmptyString, and set the value to: 1
           
      1. Right click on the CSV key, and select "New - DWORD Value"
      2. Set the DWORD Value to: DelimiterEmptyString
      3. Set the value to: 1

See Also

Keywords

Delimiter, CSV, Crystal Reports, CR, NULL, empty string, no value, export , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , BI-DEV-NET-SDK , .NET SDK / COM SDK , Problem

Product

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2011, feature pack 03 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016