ASK ANDY




FROM DATABASES TO DTP



Since the last article I've have several emails and feedback from the web site, all coincidentally, inquiring about getting graphs, spreadsheets and database data from programs such as Microsoft Office, into DTP programs such as QuarkXPress and PageMaker.

When creating documents in DTP packages, often Excel graphs, spreadsheets and database data have to be included, and getting these files into your DTP program can cause problems. These 'office' programs don't have the full graphics or pre-press features that you may need to prepare images for print, but DTP packages don't have full graph, charting or spreadsheet features as standard.

What is needed is a method to convert data to either standard graphics or text formats, and place these in the DTP program in the usual way. As an example, an Excel chart could be copied to the clipboard, then pasted into say Illustrator or CorelDraw, edited to suit, then exported from there as a standard EPS graphic. This EPS file can be placed into most DTP packages with little problem, and is a more reliable standard printing format, rather than a specific Excel format.

Other methods include outputting spreadsheet data as ASCII text, with tabs between data fields, this can then be imported and flowed into a grid of text frames, or styled using style sheets. This has the advantage that, once imported, the text can be changed and styled directly in the DTP program, rather than having to edit the data in the original spreadsheet program.

Next month I'll cover the above options and methods in greater detail, and include ways to get data from many programs, including Excel, Access, and PowerPoint into a standard useable format for DTP. As an example I was recently given a PowerPoint file, 22 slides from which had to be included in a document. There is no reliable way to import PowerPoint files directly into QuarkXPress, what I had to do was to save each slide as an Windows-Meta-File (WMF) directly from PowerPoint. These could then be imported in CorelDraw, edited to suit, exported as EPS files and simply placed into XPress.

This month I'm looking at the other approach to this, creating specific databases to help with producing various documents. Some kinds of documents, such as data sheets, directories, tables or lists are ideal to start in a database, apply formatting and styling there, then import into DTP programs for final output.

Examples could include classified adverts database for a local weekly newsletter, a TV scheduling section for a magazine, and a mail-shots for press releases etc. The formatting can take only minutes this way, rather than hours if done manually.

The examples described here feature QuarkXPress and Microsoft Access, but the principles will apply to almost any databases and most DTP programs. If your DTP program does not have a 'tags' text format, a standard format such as Rich Text Format (RTF) could be used instead, allowing text formatting to be styled within the database and automatically.

Data can input into the custom database, with no regard for bold, italic or font size formatting. When ready a macro can be run from a single click that outputs all the data (TV listings, classified adverts, etc.) as a text file, then it can be further formatted via a Word macro (just a single click), then it's all ready to import into an XPress text box. It imports with all formatting and style sheets applied automatically.

With XPress the key is using XPress Tags text format. It's similar to HTML in that it's a ACSII text format, but additional formatting can be applied via tags-codes contained within angled brackets.

When importing XPress Tags text, you must have the 'Include Style Sheets' option On, otherwise the tag codes will be imported as text themselves, rather than formatting codes.

As an example, the 'to add bold formatting', the ASCII text just needs a tag before the text:
This would be normal, This would be bold
In the above, the first turns bold on, the second toggles it back off.
The following is part of a TV listing, in XPress tags format:
@title:<$>5.35pm Dad's Army
@summary:<$>The Lion has Phones.<$> An balloon crashes into the local telephone exchange and the platoon come to the rescue.
@credits:<$>Captain Mainwaring ARTHUR LOWE
<$>Sergeant Wilson JOHN LE MESURIER
<$>Corporal Jones CLIVE DUNN
<$>Private Fraizer JOHN LAURIE
<$>Private Walker JAMES BECK
<$>Private Godfery ARNOLD RIDLEY
<$>Private Pike IAN LAVENDER
<$>Written by Jimmy Perry and David Croft
<$>Producer David Croft RPT Subtitled 588419

The @ symbol applies the style sheet name following it, so @title: applies the title style sheet to the text after it. A <$> removes any local formatting, sets italic. Style sheets can either be defined in the tags file itself, or if they already exist in a XPress document or template, only references to them are needed in the tags code.

The great thing with tags format is that many database programs can output simple ASCII and all you need to do to make it fully 'Quark formatted' is to insert the appropriate tags codes. Once you have created a database just to handle the 'data', it's not much more difficult to add the codes so that it can be output as tags format.

With Access, you can create a 'Query'-a spreadsheet like grid where data fields can be arranged and filtered/grouped via various criteria, and the tag codes can be inserted in-between, then output as a file. Once this is set up, the process is automatic (and invisible), it's setting it up originally that may take time.

As an example of a TV listing database, the data fields might be:
Date:
Time:
Station:
Program name:
Summary:
Credits:
Star Rating:

Data might be entered into this as such:

Date: 27/7/96
Time: 8.00pm
Station: BBC1
Program name: The Thrower Report
Summary: The second investigative report of the series with Debbie Thrower
Credits: BBC News & Current Affairs
Star Rating: 8
Category: news/factual

Now this data could be filtered in the usual 'database' way, so you could output say, only a particular station's programs in date and time order. It really depends on the layout for the publication. The details can be input with no regard to formatting at all, that is all handled automatically.

The 'List all BBC1 programs' query, where data fields can be grouped and sorted with criteria, and tag codes inserted too, might look like this, with comments in brackets:

"Station" Criteria = BBC1
(only output programs on the station BBC1)
"Start time" Sort = Ascending
(list in ascending starting time order)
insert:@title:<$>
(inserts tag code to apply the 'title' style sheet)
"Time" "Title"
(inserts 'Time'data, a space, then 'Title' data)
insert:
(inserts marker for new paragraph)
insert:@Summary:<$>
(inserts tag code to apply the 'summary' style sheet)
"Summary"
(inserts data from the summary field)
insert:@credits:<$>
(inserts new paragraph marker, and credits style sheet)
"Credits"
(inserts data from the credits field)
insert:@stars:<$>
(inserts code to select stars style sheet)
insert:string$("*",Val(Star Rating))
(inserts the 'Star Rating' number of star symbols)
insert:
(inserts marker for new paragraph)

Now as text here, this all looks a bit confusing, but in Access it's really a spreadsheet like grid, going horizontally from left to right. Each field required is dragged to the grid and aligned to suit. Extra non-field data can also be inserted directly.

The above 'Query' would repeat for each record in the database and produces a ASCII text file of the results. With this example the order of codes and data would look something like:

@title:<$>"Time""Title"@Summary:<$>"Summary"@credits:<$> "Credits"@stars:<$>string$("*",Val(Star Rating))

The actual ASCII file output with the field data, and markers auto replaced with real line breaks would look like:

@title:<$>8.00pm The Thrower Report
@Summary:<$> The second investigative report of the series with Debbie Thrower
@credits:<$> BBC News & Current Affairs
@stars:<$>********

This gives a fully formatted tags file, containing all the programs that fit the 'station' criteria, and when imported into a text box will apply all the formatting and styles.

It's great to see this all in action, once all the basic text is input into the database, it only takes around 3 clicks of the mouse to set the text and have it flow fully styled into XPress. (The macros take around 3 minutes to fully format 24 A4 pages of TV listing text, manually it took at least a couple of hours-it's really worth the effort to set it up in the first place).

The examples here feature databases set-up purely for publishing, but it works equally well for data already existing in a database which needs publishing. Data sheets, catalogues and directories are ideal for this kind of formatting. Rather than output the data as simple text, then apply formatting in XPress; it's far easier to insert the codes into the database, so the text is formatted automatically as it's output to the file.

Next month I'll cover the options of importing and managing already existing spreadsheet and database data into DTP programs, such as tabling and graphs. In the meantime if you have any comments or questions please feel free to email me at the address below.

Andy.

Contact: Andrew.Davidson@onyx.octacon.co.uk




Home Andy's Columns


Webmaster: aanestad@wco.com
Copyright © 1995-1998, SVCG. All rights reserved.

URL: http://www.vom.com/svcg/index.htm