- 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.
- Note: Images and data in this SAP Knowledge Base Article is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.
- SAP Crystal Reports 2011
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
- SAP Crystal Reports 2020
- SAP Crystal Reports for Visual Studio 2010
Reproducing the Issue
- Create a report based off a table which contain a database fields that has null or empty values.
- Insert the database field in the details section of the report.
- Export the report to CSV format and add a delimiter. ( Example: use a double quotes as delimiter. )
- 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:
“USA”,”First Street”, “Mark”
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:
“USA”,”First Street”, “Mark”
- 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
- 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 2011:
- Support Pack 01 and above
- Crystal Reports 2013 and above
- Note that by default, after applying the Support 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
- Close Crystal Reports.
- Open the Microsoft Registry Editor. ( Regedit )
- In MS windows, select "Run" under the menu "Start"
- Type: regedit
- In the MS Registry Editor, navigate to the path:
- For 32bit version of MS Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Export\CSV
- For 64bit version of MS Windows, and Crystal Reports 2011, 2013, and 2016:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Export\CSV
- Important Note:
- If the last part of the path does not exist, simply add the missing keys.
- Right click on the CSV key, and select "New - DWORD Value"
- Set the DWORD Value to: DelimiterEmptyString
- Set the value to: 1
- The issue is resolved in Service Pack 2 for Crystal Reports for Visual Studio 2010
- Service Pack 2 and associated MSM, MSI and ClickOnce files can be found on the Crystal Reports for Visual Studio 2010 Reference page
Delimiter, CSV, Crystal Reports, CR, NULL, empty string, no value, export , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , BI-DEV-NET-SDK , .NET SDK / COM SDK , Problem
SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020