Overview: Worksheet Design

Worksheet Design allows you to easily extract data from Excel Worksheets with multiple levels of detail.

To better understand the power of Worksheet Design, compare a worksheet with a standard format like this...

A standard worksheet.

 

... and a worksheet that displays levels of information via formatting like this:

A worksheet with groupings (or levels of detail).

 

For standard worksheets, you can just use the standard Excel connector ( Open Data > Spreadsheet > Microsoft Excel ) to easily load all your data. However, this method will not always work for worksheets with formatted levels of information.

Worksheet Design was developed to extract levels of information from "complicated" worksheets. And to do this we define trapping logic using data extraction templates.

NOTE: CSV is accepted as a valid file type that can be opened by the Excel and Worksheet Design connectors.

How templates work

To understand how templates work, consider this worksheet showing information grouped into several levels:

A worksheet with groupings (or levels of detail).

 

Customer is the highest sort level, followed by Contact, Order Number, and then Ship Date. The columns comprising the transactions are at the detail level.

Now, imagine taking a piece of cardboard and cutting holes in it at specific locations to only show specific information. You can create cutouts for the following:

• Customer (purple),

• Contact (yellow),

• Order Number (green)

• Ship Date (blue)

• Details (red)

If you then place this imaginary template on top of the Excel worksheet, only selected information will show through the holes:

The Excel worksheet, with only selected data showing through the "holes".

 

Worksheet Design uses electronic templates to extract data from your excel file. Instead of cutting holes, you define the traps and columns to be extracted.

If your report file has multiple sort levels, you will define a separate template for each level that contains data you want to extract.

After extraction, you can work with the data in Monarch Data Prep Studio. You are initially brought to the Prepare window, from where you can further prepare, transform and combine the data.

Read this page...	To learn about...
Worksheet Design Window	How to open a report in Worksheet Design The main components of the Worksheet Design main interface.
Template Definition Wizard (Guide Me)	Creating a template using Guide Me, the Excel Worksheet Design's wizard.
Understanding Template Types	Template types and how these affect how data is extracted.
Creating Templates	How to create templates manually.
All About Traps and Columns	Traps Trap components, and Columns.

 

Trapping data using Worksheet Design is explained here.