Fixed Width Formatting

[Munchie]

Hello All!

I have to create a file for export in Fixed Width format. I came up with this custom function, and want to see if anyone here has any ideas to make it more streamlined/clean. I want to post it to Brian Dunnings site as soon as it is nice and clean. The user supplies the "Text" to be put in to FW Format, the "Size" of the FW field, and the "Blanks" or empty space.

The Function is:

FixedWidth ( Text ; Size ; Blanks )

The CF is:

/*

TEXT is the text that is to be put into a fixed width format

SIZE is how big the fixed width format field is

BLANKS is what character to fill the fixed width field with after the TEXT

*/

Let(

[//X1 is supposed to have 255 spaces between the " ". The message board won't post the add'l spaces.

X1 = " " ; //255 spaces

X2 = Substitute ( X1 ; " " ; Blanks ); //Replaces the 255 spaces with the chosen "Blank Space" character

L1 = Length ( text ) //Length of the supplied text to be put in a fixed width format

];

Case(

X1 = Size ; Left ( text ; Size) ; //If the supplied text is the same as the SIZE or greater, the text gets the maximum amount of text supplied

Text & Left(X2 ; Size - L1)

)//End Case

)//End Let

Example: FixedWidth ( "Hightower" ; 15 ; "*" )

Would return: Hightower******

Thanks for the assistance!

[comment]

If you don't mind my saying so, it is rather trivial. The formula is simply:

Left ( text & filler ; size )

The way you have it, ( "Hightower" ; 4 ; "*" ) returns "Hightower", while the correct result is "High".

[comment]

BTW, there is a fixed width export stylesheet in the XML examples that are installed with the application.

[Munchie]

Holy Crap! Thanks!

I used a >= when I should have used a <=.

...Case( X1 ≤ Size ; Left ( text ; Size) ; ...

I am doing it this way, because I have to export a Fannie Mae Fixed width file. I've added a link below if you want to see what I'm talking about.

http://www.efanniemae.com/sf/technology/support/integration/integguide/data/pdf/rld321003generaldistribution.pdf

Here's the updated version, per your suggestion:

Let(

[

X1 = " " ; //255 spaces

X2 = Substitute ( X1 ; " " ; Blanks ) //Replaces the 255 spaces with the chosen "Blank Space" character

];

Left(Text & X2 ; Size)

)//End Let

Thanks for the feedback!

Edited February 19, 2006 by Guest

[comment]

I only glanced at it, but it looks like a standard fixed-width. The formula above caters for all eventualities, there's no need for Case().

With that many fields, you should really consider the XML option, instead of burdening the file with a giant calc.

[Munchie]

2 reasons I don't want to go the XML route.

1: I know very little about XML. Just enough to get Filebookslink to connect to my database, and that's it!

2: I am not sure if the software I am importing into will accept XML.

I wish I could do it though

Thanks

[comment]

OK, the second reason does not hold, because by using the stylesheet the result of the export is a text file.

As for the first reason, I know even less, but the sheet is pretty thoroughly commented, and shouldn't be too hard to modify to your needs. Just run the demo, then open the stylesheet and see.

[Munchie]

I looked at that, and I wish my situation was that easy.

The export I am doing will create an entire Mortgage Package to be imported into Loan Officer software. The export I have to do will have one package per export. Each export will have 1 or 2 borrowers. And the export format has multiple records for each borrower, each record with 2 or more fields in it.

What I am planning on doing is setting a variable for each of the records, then combine the appropriate records into a single variable, and exporting that variable using Troi File plugin.

Thanks

[Othni]

Where you able to solve this?

I am also interested in exporting fixed-length text files