Fixed length text exports using CUT Light

Tuesday 20 July 2010 @ 9:28 am

I have struggled many times while helping customers create fixed length text files from Crystal Reports. These files are standardized files used to transfer data from remote systems into centralized systems. Banks, insurance companies and government agencies often require certain business to submit data in fixed length files so they can be imported into legacy applications. The challenge has always been Crystal Reports wanting to truncate the data based on the defined page width, even though the export doesn’t actually go to a physical printer.  I recently did days of research to write an article on how to get the widest possible text export from different versions of CR.  This is published in my Expert Techniques volume 4.  Exporting to text with this method is limited in CR 2008 to 367 or 377 characters depending on your version of the export dll.  Some older versions of CR can go up to 1500 characters if you get all the right pieces in place.

But I was talking with Ido Millet about scheduling these exports when I realized that another of his tools has a very simple solution to the problem. Millet Software produces a DLL called CUT Light that adds several custom functions to the formula editor. One allows you to write a formula that can append lines to an external text file while the report is being previewed – bypassing the export feature altogether. The function is called FileAddText and this is an example of the formula I used to do a fixed length export.

FileAddText ( “C:\archive\test.txt”, {@test} , false, true)

There are four arguments.
1) The path and file name you are updating (it will be created if it doesn’t exist)
2) The string you want to append within that file
3) If you want the file to be overwritten each time
4) If you want to have a carriage return added with each row.

This process takes a bit longer than an export, but it has no limitations on length and is less sensitive to version changes, as long as the CUT Light dll is installed on the PC running the report.

I also recently wrote about a free dll that has a similar function called FileAppendLine. I haven’t tested this but it I expect it works the same.  Don’t expect support on a free product, but it is hard to argue with free.

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







Leave a Reply

Recrystallize Pro