Carriage returns in a formula that will survive a text export.

Friday 8 March 2019 @ 12:40 am

There are several common uses for exporting to text format.

I use text format whenever I need a Fixed Length export file. These are files where the exported record doesn’t have a comma or pipe delimiter. Each field in the string is identified by its character position in the string, since each field is a specific number of characters. This means that all records end at the same position, regardless of how long individual field values are.

I also use text export format for some CSV exports, because there is more flexibility.  For instance, when I need to generate two CSV rows from the same detail row in the data I find it easier to structure the CSV rows in a formula  and export as text.

And that brings me to what I learned last week. If you are exporting to “text” format and the formula you are exporting has carriage returns in it, you might find that they don’t work after the export. For instance the formula below would show 3 rows in the preview of Crystal Reports:

{@String1} & CHR(13) &
{@String2} & CHR(13) &
{@String3}

The function element CHR(13) creates a carriage return between the different elements of the string. But if you export this formula using “text” format you will find that the carriage returns don’t survive the export. The text file would not have the three rows that you see in preview. But with a little experimenting I found that adding a second related function in the formula works better:

{@String1} & CHR(13) & CHR(10) &
{@String2} & CHR(13) & CHR(10) &
{@String3}

In CR preview both formulas will appear the same. However, the second formula will provide carriage return that survives into the text export, while the first one will not.

Within a week of making this discovery for one customer, I found I needed the same thing for a second customer. I probably should have figured this out even sooner. When working text files and hidden codes I have seen that you usually need both a carriage return, Chr(13), and a line feed , Chr(10) to start a new line. But since it only takes one of these in Crystal preview, it is easy to forget that they work together.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server