[thelist] Tip: Excel flat file layout

[John.Brooking at NA.SAPPI.COM]

Joshua's recent tip on Time Tracking reminded my of a few Excel-related tips
I often use. On rereading, this seems only tangentially related to web
development, but then so was Josh's, so I'll assume it's okay. See this and
the next message:

<tip type="Excel flat file layout" author="John Brooking">

In my pre-new millennium business application world, I often need to design
a process to exchange "fixed-length" flat files with another system, such as
SAP or a mainframe. These are text files that are a constant length, and
each field is in the same span of columns on each line. For example, maybe
column 1 is a record type, columns 2-21 are a 20-character name, etc.

When writing up and sharing with others the specification for such a file, I
like to use Excel (or any spreadsheet would work) to make sure I calculate
all the starting and ending positions correctly. I maintain 3
position-related columns: StartPos, EndPos, and Length. Set the first line's
StartPos at 1 (or 0, according to preference), and put an equation in its
EndPos to be StartPos + Length - 1. (The range is inclusive of both StartPos
and EndPos.) Then on each successive line, set the StartPos to be the
previous line's EndPos + 1, and carry both the StartPos and EndPos equation
down for as many lines as you have. Now all you have to do is fill in the
lengths! You can also sum the lengths to derive the total "record length"
(which should also agree with the next starting position, were there one.)

If you insert or delete rows, you should recopy the equations from the
beginnig, as Excel sometimes doesn't adjust the equations the way you think
it would. I often start with 20-30 blank lines so I don't have to be
constantly recopying them.

</tip>

- John
This message may contain information which is private, privileged or
confidential and is intended solely for the use of the individual or entity
named in the message. If you are not the intended recipient of this message,
please notify the sender thereof and destroy / delete the message. Neither
the sender nor Sappi Limited (including its subsidiaries and associated
companies) shall incur any liability resulting directly or indirectly from
accessing any of the attached files which may contain a virus or the like.