Symptoms
When exporting a report to CSV format, you receive one of the following error messages:
With Report Commander 1.2 or earlier:
Error: An error occurred while converting the temporary XLS file to CSV format: Found a high surrogate char without a following low surrogate at index: 399. The input may not be in this encoding, or may not contain valid Unicode (UTF-16) characters.
Cause
This problem occurs in certain situations when a field in your report contains text greater than 255 characters in length.
Background
Report Commander generally uses Crystal Reports runtime components to perform data exports, producing the same output that the Crystal Reports designer produces. However, the native Crystal Reports CSV export has two significant limitations:
- It adds all fields from the report and page headers to the beginning of every record that it exports.
- It does not provide an option to include the field names in the first row of the export.
To circumvent these limitations, Report Commander does not use the Crystal Reports native CSV export. Instead, it uses the Crystal Reports runtime components to export the report to a temporary Excel file, then reads this file and converts it to the final CSV file.
To read the temporary Excel file, Report Commander uses the Microsoft Jet ODBC driver. This database driver "guesses" the data type for each field in the file by examining a few rows (8, by default) at the beginning of the file. If, for a given field, none of the records that the driver examines during this phase has more than 255 characters, the driver assigns this field a data type that is limited to 255 characters. If, later in the file, there is a record where this field contains more than 255 characters, the database driver truncates the value while reading it, leading to corrupted data.
Workaround
There are three ways to avoid this issue. The first is the recommended method for most users.
Use the Native Crystal Reports CSV Export
Beginning with Report Commander version 1.2 you can instruct Report Commander to use the native Crystal Reports CSV export.
To do so, select the "Crystal Reports Style" value for the CSV Formatting option in the Report Commander Command Line Builder, or use the option "-csvstyle=crstyle" on the Report Commander command line. You must also remove the "-firstrowheaders" option if it is present.
Note that if you choose to use the native Crystal Reports CSV export, you will need to suppress all headers and footers, and you will not be able to produce an export file that has the field names on the first row.
Help the Database Driver Interpret Your Data
If some rows will contain fields with more than 255 characters, make sure that at least one record with more than 255 characters in those fields appears within the first eight rows of your output. If you do this, the Jet database driver will assign the correct field type for your data and will be able to read it properly.
Force the Database Driver to Scan Your Entire File
By default the database driver only examines the first eight records of your report and determines the field data types based on the data in those records. You can force the driver to scan the entire file to determine the data types, which will cause it to recognize your long fields. Note that this option may slow down the export process.
To do this, run the Registry Editor and go to key HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Excel. Edit the TypeGuessRows value and set it to "0".
For more information about this change and its effects, see Microsoft Knowledge Base article 189897.
More Information
For more information see Microsoft Knowledge Base article 189897.
Status
For Crystal Reports XI Release 2, the native CSV export for Crystal Reports has been improved. The upcoming Report Commander version 1.3 will use this new capability to eliminate the problems described above.
