Formatting text output (97)

Topic

New Reply

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 97 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.

Reply | Quote

Replies

(Edited by carbonnb on 29-Nov-02 13:56. To reorder a couple of paragrahps, so it actually makes sense.)

I don’t know of anything like this.

But if you were up for a challenge, you could roll your own.

This is just a conceptual idea, but it could go something like this:

You would have a way to write an export template, something like:

“[field1}{tab}{quote}{field2}{quote}{NewLine}{tab}{tab}{field3}”

This would your template. You pass that, the recordset and the output file to a function that is a series of replace commands.

Replace strTemplate, “{Quote}”, “”””

This would replace the {quote} tag with a quote mark.

You would also have to loop through all the fields in the recordset to, as well as all the records in the recordset

Like I said, this is just conceptual. None of it is actually tested.

Reply | Quote

I have been thinking about this a bit more and I would probably make it a class module, the function to export, that is.

That way you can set the parameters at various stages(i.e. the export file name, a file header, a file footer, a record header a record footer, the recordset…) and then once all the pieces are in place, you write out the text file.

Reply | Quote

This question was cross-posted to the VB/VBA forum here

Reply | Quote

I’m not completely sure that I was following your request, but…..
We have a tool that we created in Access for collecting Application Requirements. The “requirements are then exported to a WORD document. But to get from Access to Word and have the final document with appropriate formatting, we had to:

Run an ACCESS query that outputs information in a specific order (determined by our final output needs). We placed ~ marks to break up the fields. The final output was saved as a .txt file. The file output looked something like this:

2~Functional
3~COMMON STUDY REQUIREMENTS
4~Log In
5~The system must allow the user to log in to CDAS. $$ID=8$$
6~The system must allow the user to enter a username and password. $$ID=10$$

The first outline number told me what outline level to assign.
I then created a WORD template that imported the .txt file, and ran a macro that formatted the text. Using the first number to assign outline number levels, I formatted everything by use of search/replace styles. The $$ around the ID number were used because I couldn’t search for () since they represent wildcards.

After the Find/Replace formatting, the .txt info looked like:
3.1.1 COMMON STUDY REQUIREMENTS
3.1.1.1 Log In
3.1.1.1.1 The system must allow the user to log in to CDAS. (ID=8)
3.1.1.1.1.1 The system must allow the user to enter a username and password. (ID=10)
3.1.1.1.1.2 The system must only allow authenticated users access to CDAS. (ID=12)
3.1.1.2 Site Map

So, you should be able to create styles associated with various sections, export from ACCESS to a .txt file, and then insert the contents of that file into a WORD Document. Use find and Replace to assign appropriate styles.

Reply | Quote

Sounds like a perfect spot to use Implements, if I have ever heard one.

Implements lets you create one interface, which can use multiple classes.

You could build a blank class (read MSDN articles about Implements) that would replicate functions similar to ADO (RecordCount, EOF, BOF, MoveFirst, etc.), then build individual classes for each type of format. That way, you can use the Implements class in all of your code to interact with the data, and to allow the class to interact correctly, you simply tell it which type of implements to use.

Drew

Reply | Quote

Did Access 97 support Implements? I know VB5 did but I didn’t think VBA 5 did.

Reply | Quote

No, I don’t think so. I was thinking more along the lines of doing that in VB 6.0, creating an ActiveX control, and using that within Access. ( Because you never know when you would want to interact with that data from something else, like VB, ASP, etc, and if they are in a .dll, you just reference it.)

Drew

Reply | Quote