I’m looking for solutions, suggestions and bright ideas. I’m producing a
relational database system that outputs various subsets as input data for
nine different legacy systems. The “client” systems all take ASCII text
data, but are very picky about its format – for example, a client may expect
a record to be split over several lines of the output file in a particular
way, with some of the data items in quotes and some not, some separated with
commas and some not, different items to different decimal places and so on.
Some clients expect a separate file for each “object” in the database, while
others expect a single file with record “headers” and “footers” to separate
the objects. In all there are 56 formats.
For my pilot study, I’ve used an Access database, extracted the appropriate
data with SQL and used VBA code to format the outputs, but it’s required a
lot of code to do nothing terribly clever and an immense amount of testing
to check that it always does the right thing, whatever the data in the
database.
It seems to me that I shouldn’t have to program each format separately.
Having extracted the right data using SQL, there should be a way of
formatting it according to a “template”, predefined for each output format.
(Even Word’s MailMerge would almost do this!) Can anyone suggest an
off-the-shelf solution for this, or a more appropriate technology than the
one I’m using, or point me in any promising directions. (BTW I also have to
bodge the data, for example alter numbers that are outside allowable ranges,
but I’ve already written the functions to do this, and they could be called
by the queries if necessary, rather than as part of the “formatting”.)
The solution needs to be _very_ robust and maintainable for years.
