Data Extraction Tips for Monarch

by Sandy on October 23, 2007

in Extraction Templates

Mining for Gold

In all likelihood, Monarch is most often used to extract data from report files. After all, that’s its specialty.

Unfortunately, this most fundamental aspect is often the program’s most challenging task. Sometimes it seems that it’s more art than science. Many would say that labeling this process as a “craft” would be more accurate, and I’m inclined to agree.

Just as a fine craftsman, say a wood carver, knows exactly which tool to use to execute his vision, you’ll need to be intimately acquainted with the template process and tools. Of course, you won’t need to use every tool every time, but you’ll know which tool you need to use and how to use it. Seem reasonable?

Let’s look at what’s available to us in v9 Pro. The template window has five areas:

  1. The template name and buttons
  2. The template type information
  3. The tools area
  4. The trap line
  5. The sample line(s)

Area 1

Always take a moment to properly name your template. It might not seem that important for simple models, but you’ll be glad you did it right when your model grows, or especially when you’re using object linking later on. Be specific. What will “Detail” mean when you have 100 models that all have a “Detail” template? Wouldn’t “Shipping Report Details” be better?

If your template is even slightly complicated, take a moment and document it with a comment. Explain why it is setup the way it is. Even you might not understand it if you come back to it in six months.

We’ll look at links in detail in a later post.

Area 2

What’s the purpose of these different template types?

  • Page Header: used to extract data which occurs at the top of every page, like page numbers or run dates. (Or to create a virtual page if the need arises.)
  • Append: used to extract data within the body of the report that we want to associate to the detail data. Append data usually appears before the detail data on the report, though there’s a neat trick you can use to use an Append template to capture data which appears after your detail data.
  • Detail: used to capture the lowest level of detail in your report. This is generally the basic information that you expect to appear for each record you want to extract.
  • Footer: used to capture data that appears after your detail data.

The checkbox for Floating Trap lets you tell Monarch that the trap you’ve defined (we’ll talk about that next) might move around within the line on the report a bit, so don’t always expect it to be exactly in the column position where you defined it.

Area 3

This is where the bulk of the tools lie.

The text box for the Trap Line refers to the line number of your sample in Area 5. Typically report layouts mean you’ll be trapping based on the first line of the sample, so the default value of 1 will be correct. But sometimes the best way to trap will be based on another line in the sample, so be sure to change this value to be the correct line number, or your trap won’t work as you intended.

The next icon, Replace Sample Text, lets you change the sample appearing in Area 5. Perhaps you selected four lines, but need five. No problem. Simply select the five lines you from the report that you want in the sample, and then click the Replace Sample Text icon. Area 5 updates based on your selection. This is also a good tool if your report changes to add, move or remove fields. The most important thing about the sample you use is that it defines the minimum number of lines between occurrences of the template trap line in the report. By comparison the data in the sample is not at all important EXCEPT when working with floating traps (for trapping purposes) or if you want Monarch to autocreate fields for you. I’ll come back to these topics in a later post.

The Alpha trap puts an alpha character in the trap line on the current column position. Typically you’d first click in Area 4, the trap line, where you want the trap to appear, and then click the trap tool (Alpha, Numeric, Blank, Non-blank, Numeric OR, Postal Trap or Not Trap icon.

You use the Alpha trap to tell Monarch that you expect a letter to appear in this position.

The Numeric trap tells Monarch that you expect a number in this position.

The Blank trap tells Monarch that you only want lines where nothing appears in this position.

Alternatively, the Non-blank trap tells Monarch that you want lines where something, anything but blank, appears in this position.

You use the Numeric OR trap anytime you expect to see at least one digit in any number of specific positions. As long a number appears in any one of those positions, Monarch will trap that line.

The Postal trap inserts “<ZIP>” into the trap line at the leftmost position. This trap icon is really only still around to support older models, and was designed to help extract address data. This trap functionality has been replaced by the “Address Block” feature, which you should use instead, so I won’t elaborate on the Postal trap further. We’ll cover the Address Block feature later on, I’m sure.

To use the Not trap, you first highlight the trap in the trap line (Area 4). When you do that the Not icon will change from disabled (nothing happens when you click it) to enabled. Clicking the Not trap will change your trap from black font to red font, indicating that Monarch will only trap when the line, in the indicated position, does not show what the trap is. You need to be a bit careful when using the Not trap in multiple positions, or you may not get the results you expect.

The next icon Reset Trap will clear the trap line in a single click. Start over!

The next set of icons relate to the sample area (Area 5). The Auto-Define Fields icon will take an educated guess at which fields in your sample area you want to capture based on your trap. While often useful, the results aren’t always perfect. It does work quite well, though, as long as your fields are preceded with labels ending in a colon character, like “Name:”.

The next icon displays the field properties of the active field. You must either paint (we’ll talk about that shortly) a new field, or click in an existing field to use this icon.

The next icons erase defined field areas. You use these if you don’t want to capture the defined field(s).

Finally the arrow icons allow you pull your defined traps and fields to the left one position, or push them to the right one position. When you pull left, anything right of the current cursor position shifts to the left, anything left of the current position doesn’t move. When you push right, anything right of the current cursor position shifts right one position and anything left of the current position doesn’t move.

Area 4

Now that we’ve reviewed how the various tool icons insert characters into the trap line, you need to know that you’re not limited to those traps. In fact, you often don’t need to use any of them to trap properly.

Sometimes you can just type words or symbols (literals) in the right position. You might have a trap as “Customer”. Or a date trap that uses forward slashes only to trap dates like 02/01/08, knowing that two / characters separated by two spaces will do the trick.

Some tricky reports require just the right combination of trap tools and literals to get what you want and only what you want. This is where the craft comes in (you knew that had to come up again, didn’t you?).

Trapping well requires that you recognize the specific recurring patterns within your report that allow you to differentiate a data line from another irrelevant line on the report.

Experienced Monarch users will probably chuckle a little at this next comment. When you start modeling a number of reports, you may well find that when someone hands you a printed report you don’t initially read it for its content, you’re scanning to see what you can trap. Or maybe that’s just me. LOL!

Sometimes you may find that because of the design of the report it is impossible to build traps that will capture what you want without picking up some that data that you don’t want. Or it’s simply taking too long to model that particularity. No problem. Go ahead and capture everything. Monarch provides other tools to solve that problem, but that’s a discussion for another day.

Area 5

You’ve worked hard to isolate the data within the report. Now’s the time to define which data you want to capture within the sample you selected.

To do this you simply click and drag your mouse over the text or values in the sample area. This is called painting your fields. Once you have defined the field, you can double click it, right click on it, or click the Field Properties icon to bring up a dialog box that lets you name the field, set its data type, and other options. We’ll delve into the other options in another post.

That’s a fair bit of discussion for one day. If you’re new to Monarch, I hope this helps makes you a bit more comfortable and that you’re that much closer to thinking “I excel with Monarch!”

Leave a Comment


Previous post:

Next post:

Copyright © 2007 - 2015 Excel with Monarch Training and Services. All rights reserved. Privacy Policy
Microsoft Excel™ Microsoft Corporation. Monarch and Monarch Pro™ Datawatch Corporation.