Create Fixed Width (Space Delimited) Text File

[jmiskey]

I need to create a data export file using Crystal Report that exports a file in Fixed Width (Space Delimited) Text format.

Without doing a lot of "trial and error", is there any easy to designate how many spaces each field should take up? It appears that the default ruler in Crystal Reports is in inches. Is there some way to convert it to postitions (or spaces)? Or do I need to change a font size?

Any tips would be appreciated. By the way, I am using Crystal Reports version 10.

Thanks.

 

[Ridyen]

Well I don't know if there is an easy way to do it or not. However you may be able to create a Crystal Report with a single field of data that you have created in the desired format. For example given a simple table with two fields you could create a very simple formula like:

fieldA + " " + fieldB

Place the formula on your detail line and there you go. Now if either field has a variable number of characters in it you will have to decide on the maximum number of charaters in the field and either truncate those that are too long or pad those that are too short with spaces. An example formula to accomplish this follows:

StringVar theName := "" ;

if len({PERSON_EXPORT_ALL.Last_NM}) > 4 then

theName := left({PERSON_EXPORT_ALL.Last_NM},5)

else theName := {PERSON_EXPORT_ALL.Last_NM} + space(5 - len({PERSON_EXPORT_ALL.Last_NM}))

Of course you might have to make a formula for every field that you want to export.

Finally create a single formula that assembles the entire output. Something similar to the following:

{@theExportName} + " " + {@theExportID} + " " + ......

And you end up with an output string that looks like the following when exported to a text file:

John 123 data .....
Randy 789 data .....
Micky 1 data .....

Of course you may have to deal with nulls and empty fields as well. Since I have no idea on the size of your records, their format, complexity, etc. this may be silly approach for you. Like you I have found no other easy way to deal with it.

 

[jmiskey]

Thanks Ridyen. I don't think there are a lot of fields on the export, so that workaround may work.

 

[Madawc]

Ridyen has the right approach, but there may be extra problems when you come to try it.
Crystal likes to adjust for what it thinks you want, so getting a fixed-lenght export is tricky. I got it working by putting the fields all together in a large formula field, making it Courier and also changing the 'Characters per inch' to 15 on Excel.

Null fields can be handled using formula fields to test and produced a blank value for such a case.

If you have numeric values, you'll probably need to use something like ToText({number}, "000000")

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10