Exporting to Fixed-Width Formats

Many computer systems exchange data in some form of fixed-width format. This term refers to formats in which an individual field always contains a certain number of characters of data. Data that's too wide for the field width is sometimes truncated to fit. Data that takes up less space than the field width allows is padded with a padding character, such as a zero or a space, to bring it up to the specified width. For example, the number 797 in a 10-character fixed-width format might be rendered as "0000000797" (left-padded with zeroes). The name Tomczak displayed in a 15-character fixed-width format might be displayed as "Tomczak" (right-padded with spaces). Fixed-width formats also sometimes simply run all the columns together into a single big fixed-width string. There's no need for internal field separatorsbecause the exact width of each field is known, it's easy to determine where each field's data starts and stops.

If you need to export FileMaker data to a fixed-width format, you'll need to do a bit of work by hand; FileMaker has no built-in support for exporting to a fixed-width format. At a minimum, you'll need to define some calculations to perform padding and concatenation. If you want to build a more permanent framework for working with fixed-width data, you can consider developing a small library of custom functions to do some of the work.

Padding data is a straightforward activity using FileMaker calculations. Say you have a number field called OrderTotal. To left-pad this number with zeroes and enforce a fixed width of 10 characters, you would use the following calculation:

Right( "0000000000" & OrderTotal; 10)

If you think about that for a moment, it should be clear how it works. The calculation tacks 10 zeroes onto the left of the numeric value, and then takes the rightmost 10 characters of the result. Likewise, to right-pad a text field called FirstName with spaces to a width of 10 characters, the calculation would look like this:

Left( FirstName & " "; 10)

Finally, if you needed to run a set of these fields together into a single fixed-width row, a calculation that concatenated all the individual padding calculations together using the & operator would suffice. You could also create a single row-level calculation without bothering with individual calculations for each field:

Right( "0000000000" & OrderTotal; 10) & Left( FirstName & " "; 10)

Calculations such as these will work fine for simple or occasional fixed-width exports. FileMaker also ships with an XSL style sheet, called fixed_width.xsl, that can be applied to a FileMaker data set on export to produce a fixed-width export. The style sheet supports only a single fixed width for all output columns. For more complex needs, you can build a tool of some sort to streamline the process.