Problem Exporting Crystal Report to Text File

[richf007]

Crystal Reports XI.

I'm having problems exporting a large amount of data (i.e. many fields) from Crystal to a text file. I created a formula to assemble all of the required fields, which must be fixed length to match a mainframe system (formula is below). Each sub-formula makes the corresponding data field the correct size (e.g. pad the last name with spaces to make sure it's 20 characters).

I think there are just way too many characters for Crystal to handle. When it's placed on the detail line nothing gets displayed in the preview pane, and nothing gets exported to the text file either. If I start to remove fields (i.e. comment them out of the formula below) the data appears after commenting out Addr2 to the last SPACE at the end.

I've tried a bunch of things with page setup and font but no luck (including some suggestions from similar questions/answers posted at Tek-Tips). Not sure if there is a solution but I'm out of ideas, so any suggestions or comments will be appreciated.

Thanks.

// Format all of the fields required for the report and combine them into 
// one field for printing so when the report output gets exported to a 
// comma delimited Text file it will match the caller file format requirements.
{@f_SSN} + "," +             // SSN            PIC X(10) VALUE SPACES
space(10) + "," +            // PAYROLL-EID    PIC X(10) VALUE SPACES
space(10) + "," +            // RETIRE-EID     PIC X(10) VALUE SPACES
space(04) + "," +            // PIN            PIC X(04) VALUE SPACES
space(01) + "," +            // BARG-UNIT      PIC X(01) VALUE SPACES
space(06) + "," +            // DEPT-CODE      PIC X(06) VALUE SPACES
space(01) + "," +            // BENEFITED      PIC X(01) VALUE SPACES
space(04) + "," +            // EMP-STAT       PIC X(04) VALUE SPACES
space(08) + "," +            // HIRE-DATE      PIC X(08) VALUE SPACES
space(01) + "," +            // CITY-SDDPC-CD  PIC X(01) VALUE SPACES
{@f_LastName} + "," +        // LAST-NAME      PIC X(20) VALUE SPACES
{@f_FirstName} + "," +       // FIRST-NAME     PIC X(15) VALUE SPACES
space(03) + "," +            // SUFFIX-NAME    PIC X(03) VALUE SPACES
{@f_MiddleInitial} + "," +   // INIT-NAME      PIC X(01) VALUE SPACES
space(04) + "," +            // TS-LEVEL       PIC X(04) VALUE SPACES
space(10) + "," +            // RETIRE-PLAN-ID PIC X(10) VALUE SPACES
space(01) + "," +            // 401K-CURR-CONT PIC X     VALUE SPACES
space(01) + "," +            // 401K-BAL-IND   PIC X     VALUE SPACES
space(01) + "," +            // SPSP-CURR-CONT PIC X     VALUE SPACES
space(01) + "," +            // SPSP-BAL-IND   PIC X     VALUE SPACES
space(01) + "," +            // SPSP-M-CURR-CONT PIC X   VALUE SPACES
space(01) + "," +            // SPSP-M-BAL-IND   PIC X   VALUE SPACES
space(01) + "," +            // SPSP-H-CURR-CONT PIC X   VALUE SPACES
space(01) + "," +            // SPSP-H-BAL-IND   PIC X   VALUE SPACES
{@f_Addr1} + "," +           // ADDRESS-LINE-1 PIC X(30) VALUE SPACES
{@f_Addr2} + "," +           // ADDRESS-LINE-2 PIC X(30) VALUE SPACES
{@f_Addr3} + "," +           // ADDRESS-LINE-3 PIC X(30) VALUE SPACES
{@f_City} + "," +            // CITY           PIC X(30) VALUE SPACES
{@f_State} + "," +           // STATE          PIC X(02) VALUE SPACES
{@f_ZipCode} + "," +         // ZIP            PIC X(09) VALUE SPACES
space(57)                    // FILLER         PIC X(57) VALUE SPACES

 

[AndyMc]

Richf007,

Try putting the fields (or individual formulas if the need the space fills) on the report individually and export Comma seperated (CSV).

Andy

 

[richf007]

Thanks Andy...I'll give that a try.

 

[IdoMillet]

The problem is probably because some of the fields contain nulls. Concatenating a null to a string makes the whole string a null. So use IF IsNull() formula logic to convert nulls to "".

Also, remember to set the characters per inch option to a high value.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[CoSpringsGuy]

it looks like you are doing very clsoe to what I am doing. I need a fixed length file to be exported. Andys suggestion is exactly what I do with one added suggestion. When exporting to CSV you are prompted for the delimiter and the seperator.... make sure both of those are blank.



_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[richf007]

Thanks to all that replied. We (me and a co-worker) offerred 2 solutions to the client agency requesting this info:

1. Created a comma delimited text file containing all of the fields we have available, but not in the same size the client requested. The IsNull suggestion worked great on fields that had no data, e.g. Address2, Address3. Created a psuedo COBOL program for the client to read our file and load data into their required format/file. This program will set all of the fields in the output file to spaces first, then move in our fields. This allows us to move our fields, which are shorted in length, to their fields and have them padded with spaces. It also allows us to set the fields we don't have to spaces, which is what we needed to do anyway. The end result is the client agency gets their file by adding 1 simple COBOL program to the process.

2. A co-worker developed SQL to extract the same data from another database into a new format submitted by the client. This puts the output data into a COBOL format ready for the client to use by a different program. The end result is the client gets a file for input to their process via another avenue or input stream.

We'll let the client decide which option they prefer and then send them the file.

Again, thanks to all for your help, suggestions, comments, etc.