Export to Fixed Width text file

[icodian]

Good Wonderful Monday Morning!

I am working on a report that will be used for exporting payroll information into a Fixed Width ASCII text file. I have around 25 fields that need to be exported. A quick example of the format is as follows:

Field Start Length
SSN 1 11
MiscValue 12 2
LastName 14 36
FirstName 50 20
MiddleInit 70 1
etc.

1. Anyway, can anyone suggest an easy way to do this through a Crystal Formula? Delimited exports are easy enough, but the fixed width is giving me a problem. I have found one way to do this in a knowledgebase article but it is tedious to do with 25 fields. (

http://support.crystaldecisions.com/library/kbase/articles/c2005609.asp)

The article involves checking the current length and specifying a required length. Use an IF statement to compare them. Truncate the field if the current length is longer or replicate spaces if the required lengh is longer.

I was wondering if someone might know a function to accomplish this in a better and more efficient fashion.

2. Additionally, the 25 fields run over 400 characters. I believe a string formula can only contain 255 characters max. Assuming I can get the fixed width working, can anyone suggest a way to start at character 256 with a second formula?

Thanks so much for the time!

 

[Reebo99]

First, unfortunately I've found that the if then else, len(), space() solution to be the best. An example of how I've used this is :

// Charge Account Number from message - 8d
{MESSAGE_DR.DBT_TER_IDKEY}&
// Year - 4d, Month - 2d and Day - 2d from TRN (8d total)
ToText({MESSAGE.TRN_DATE},"yyyyMMdd&quot&
// Transaction Reference - 16a
ToText({MESSAGE.TRN_DATE},"yyMMdd&quot&{MESSAGE.TRN_NUMBER}&space(10-Len({MESSAGE.TRN_NUMBER}))&
// Blank (To be completed by IMS) - 1a
Space(1)&
// Message Type = S - 1a
"S"&
// Source = F - 1a
"F"&
// Year - 4d, Month - 2d and Day - 2d from TRN (8d total)
ToText({MESSAGE.TRN_DATE},"yyyyMMdd&quot&
// MT Type - 3a
{MESSAGE.TYPE_CD}&space(2-len({MESSAGE.TYPE_CD}))&Left({MESSAGE.SUBTYPE},1)&space(1-Len(Left({MESSAGE.SUBTYPE},1)))&
// BIC address of Sender - 12a
{BANK.SWIFT_ID}&space(12-len({BANK.SWIFT_ID}))

Secondly, Which version of CR are you using. CR9 has a limit of 65534, however below v9 the limitation is 255. Hopefully, you are using v9 and this issue will go away.....

Reebo
Scotland (Sunny with a Smile)

 

[synapsevampire]

First, the limitation is 254, not 255 in CR 8.5 and below.

The simplest solution might be to create 2 formula fields of aprox 200 chars each and drop them into a text field, which should provide output as contiguous data.

As for not explicitly ensuring the length of the field, I think that you'll have to.

-k

 

[synapsevampire]

First, the limitation is 254, not 255 in CR 8.5 and below.

The simplest solution might be to create 2 formula fields of aprox 200 chars each and drop them into a text field, which should provide output as contiguous data.

As for not explicitly ensuring the length of the fields, I think that you'll have to.

-k