Overview: Creating Data Extraction Templates

What is a data extraction template? Imagine taking a piece of cardboard and cutting holes in it at specific locations. If you then place this imaginary template on top of a printed report, only selected information will show through the holes (see the graphic below). Monarch Data Prep Studio uses electronic templates to extract data from your reports. Instead of cutting holes in the template, you paint the fields to be extracted.

Many reports are sorted on multiple levels. The items shipped in the report shown below are sorted by ship date within customer. In this case, customer is the highest sort level and ship date is at the next highest level. The fields comprising the transactions are at the detail level. If your report file has multiple sort levels, you will define a separate template for each level that contains data you want to extract.

A report with multiple sort levels.

 

Detail and Append Templates

To extract fields from a report, you must begin by defining a detail template. A detail template extracts fields from the lowest sort level in the report, referred to as the detail level (also known as the transaction level or itemized level). The detail level contains the data that changes most often in the report. Monarch Data Prep Studio generates one database record for each detail line extracted from the report.

In addition, you can define templates for the higher sort levels, called append templates. An append template adds, or appends, information from higher sort levels to each record extracted from the detail level. If append templates are necessary, they can be created in any order. You may define up to twenty append/footer templates to extract data from higher sort levels, plus a page header template to extract page header data and a footer template to extract footer information, such as totals.

Assembly of database record from a detail template and append templates.

 

Fields extracted using an append template are associated with the detail record if the append level begins anywhere on or before the first detail line. The extracted field values are appended to all subsequent detail records until the next occurrence of the append field in the report. For example, in the illustration above, a separate detail record is created for each transaction. The customer name, contact and ship date fields, which are extracted using append templates, are associated with all four records.

Page Header Templates

Many reports have titles, page numbers, dates and other fields that appear at the top of each page. This is called the page header. Monarch Data Prep Studio uses a special template to capture useful page header information.

In the illustration above, the report contains a three line page header with several fields that you might want to extract, including the report run date and the page number. The page header template lets you extract these fields and append them to each of the detail records that follow, until a new page header is encountered.

Caution: Page header templates should not be used to extract fields that appear other than at the top of each page. Use an append template to extract fields that may appear elsewhere on the page.

GROUP Footer Templates

Group Footer templates are used to capture fields that appear after detail lines. Group Footer templates work just like append templates, except the field values are appended to the detail lines which precede them.

Note: Monarch Data Prep Studio allows multiple group footer templates.

The Total field may be extracted using a footer template.

 

EXCLUSION TEMPLATES

Exclusion templates are used to specify lines or parts of lines that you do not wish to be captured by a detail, append, header, or footer template.

 

In the figure above, for example, the red line marks an exclusion template. This template will allow the order number and ship date of every record to be captured even if a colon is used to trap these details.

For more information, see Creating an Exclusion Template.

Start and End Region Templates

Start and End Region Templates define portions of a report where all other types of trapping should begin and end, respectively.

 

For more information on these templates, see Creating Start/End Region Templates.

 

Viewing extracted data in Monarch Data Prep Studio

When Monarch Data Prep Studio extracts data from a report, the data is placed in a database table that you can view and manipulate in Monarch Data Prep Studio's Prepare window. The database table is comprised of rows and columns of data, with each row representing a database record and each column representing a field.

Monarch Data Prep Studio assembles records automatically as you define detail and append templates in Report Design. Fields from the detail template serve as the nucleus for each record. Fields extracted by the append, page header and group footer templates are appended to each record.

Each detail line from the report produces a record in the table.

 

While creating your template, you can get a preview of what data is extracted by viewing a subset of the records in Report Design's Data Preview panel.

The Data Preview panel provides a preview of how data will be extracted.

 

See Creating a Template for related information on creating templates.

See the Template Editor and the Template Editor Action Bar for more information on the tools you can use to create a template.