Record Style, Separated Values, Tab Separated Text, Text
Crystal Reports gives you a variety of ways to export reports to text files. The benefit to exporting to text files is that virtually any program that lets you import data can read text files. They are the least common denominator among software programs for sharing data.
The only drawback to using text files is figuring out the best way to format the data so that it can be imported by your program. The most common format that programs import is CSV (comma separated values). With the CSV format, commas are used to separate each field. Another popular format is tab separated. Let’s look at each of the different types of text files that Crystal Reports exports to and find out how they differ.
When Crystal Reports XI exports to CSV files, the page header is repeated at the beginning of each detail row in the report (it even happens when the Detail section is suppressed). To get around this problem, Business Objects recommends that you trick Crystal Reports by creating a false group header around the entire report and putting the report header fields in this new group header. Unfortunately, this is not always successful. If you want the detailed steps for doing this, go to the Business Objects support site (support.BusinessObjects.com) and search for the knowledge base article c2014451. This has been fixed for Crystal Reports XI R2 and I suggest that you upgrade to it if you are exporting to CSV. The details of how to fix this are in the XI R2 note at the end of this section.
The Record Style format only exports fields from the group and detail sections. It does not export the other header and footer sections. It treats each field as a fixed length string and always uses that number of characters for the field. If the field doesn’t need all the characters allocated to it, then it fills the remaining characters with spaces. For example, if a customer name can be up to 25 characters long but the name is only 11 characters long, then there will be 14 spaces saved with it.
When importing a record style text file into an external program, you usually tell the program how the record is structured. Since there are no separators between the fields, the program importing the file doesn’t know when one field ends and the next field begins. Each record is just one long row of characters. You have to define the fields in the table by specifying the starting point and length of each field. Then the external program importing the data knows how to parse each field out of the record.
The Separated Values format exports fields from all sections of the report. This includes all the headers and footers (report, page, group) and the detail section. Each field is separated by a character that you specify. The most common character is the comma, but you could also use the Tab character or any other character you specify. The export options dialog box, shown in Figure 15-7, let’s you set the separator as well as the character delimiter.
Figure 15-7. Character-Separated Values export options dialog box.
The top portion sets the delimiter used around strings. In most cases, you will surround a string value with two double quotes. However, if there is some other delimiter you wish to use, just enter it into the top input box. The second option is the separator between fields. It uses a comma by default, but you can specify any character or use the Tab character. The last two checkboxes let you set whether the number and date values are formatted exactly as they appear in the report.
The last two formats, Tab Separated Text and Text have a unique approach to exporting. They basically reprint the report as it appears within Crystal Reports. The output file mimics the layout of the report as it is designed. While the other formats we’ve discussed so far were more concerned with exporting raw data and all the data appears on a single row, these two formats create an output file that keeps that layout of the original report and is more visually appealing. The top of the file shows the report and page header and this is followed by the group headers and then the detail section. Lastly, all the footer sections are exported just like the regular report. Of course, even though the report layout is kept intact, the formatting is not exported because the file is just a basic text file.
There are two differences between the Tab Separated Text format and the Text format. The first separates each field with the tab character. So even though the report layout stays the same, all the fields are much closer together because they are only separated by the tab character. The Text format uses spaces to separate the fields from each other. Just like the record style format discussed earlier, each field has a fixed number of characters allocated to it and spaces are used to fill in the gaps. The text format is more visually appealing and “human friendly” because the extra spaces help position the fields with the same layout as the original report.
Crystal Reports XI R2 has a new dialog box for exporting reports to the CSV format. It is shown in Figure 15-8 below.
The top portion of this dialog box has similar options as Figure 15-7. The bottom half has the new options. The Mode option gives you the option of Standard Mode or Legacy Mode. The Mode option was created to resolve a common problem with previous versions of Crystal Reports. When exporting reports to CSV, the report header information is repeated at the beginning of each detail section. By choosing Standard Mode, this problem has been eliminated and the report header is no longer exported. If you choose Legacy Mode, the report header will be exported the same as it was in previous versions. In most cases, the new Standard Mode option will give you the best data exports.
The bottom half of the dialog box allows you to conditionally suppress sections from being exported to CSV. If you only want the detail data printed with no other section information, choose the Do Not Export option for all sections.
Figure 15-8. CSV export dialog box for Crystal Reports XI R2.
