Sign in
< Back to User Guide Top

Customizing Spreadsheet Schemas (v2)

Use the Spreadsheet Schema editor to format data and determine how Clarify reads from a source spreadsheet or writes to a target spreadsheet.

Using the Schema section of the editor

Worksheets, Areas, Rows and Cells can be added,edited, and deleted from within the Schema section, and then defined from the Properties view.

The table below describes the navigational buttons and drop-down menu available.

Button Description
Add Worksheet  Adds a Worksheet in the Item column of the Schema section. This can populated with additional Areas and cells. Define the selected Worksheet in the properties view.
Add Unbounded Area  Adds an Unbounded Area to a selected Worksheet. This is used for data that repeats. An example might be Item Details.
Add Fixed Area  Adds a Fixed Area to a selected Worksheet. Fixed Areas are used for data that appears once in a known location. For example, Header, or Summary.
Add Row Group  Adds a Row Group to a selected Area. Define the selected Row Group in the properties view.
Add Row  Adds a Row to a select Row Group. Define the selected Row in the properties view.
Add Cell  Adds a cell to a selected Row Group or Area. Define the selected Cell in the properties view.
Expand/Collapse All  Expands the Schema to display all Areas and Cells within a Worksheet(s); Collapses the Schema so that no Areas and Cells are displayed.
Remove  Removes selected Worksheet, Area, or Cell from the Schema.
Format Use this drop-down menu to select the spreadsheet extension type. Options are XLS or XLSX/XLSM
Note: These same options can also be selected with the right-click of the mouse.

 

Using the Properties View

The Properties view displays different options based on the levels (Worksheet, Area, Row,Cell) selected in the Schema section. This is how to view and define properties for spreadsheet data structures.

Sample section of the editor

This section displays the actual data that was used to create the Schema. This is useful to reference when working in the Schema section. While you can not edit the data from this section, you can use the Select Sample File button to select a different sample file and see how it appears with the Schema applied to it.

Defining Properties at the Worksheet Level

Different properties for a Worksheet can be defined using the Properties view. These include:
  • Description (Worksheet Name and Description).
  • Excel Info (Worksheet Name and Override Read Position)

The Override Read Position option is used in source schemas when reading data containing multiple Worksheets. The default setting is 0; this causes the Ruleset to read the actual Worksheet name (as defined in the Excel Info section of the Properties view). However when the position is set to a non-0, then the Ruleset reads the Workbook accordingly. For example, a setting of 5 means the Ruleset will read the 5th sheet in the Worksheet. This is used when multiple Worksheets exist, and you want to ensure they are read consistently, as per your Schema configuration.

Defining Properties at the Area Level

Different properties for an Area (Unbounded and Fixed) can be defined using the Properties view. These include:
  • Description (Name and Description)
  • Area (Offset from Area and Offset Row Count)

Use Offset from Area and Offset Row Count together to set where an Area begins after a previous Area ends. Since the exact number of Rows that Unbounded data may require won’t always be known, these two settings can help ensure where the Rows on the next Area begin. This applies to both source and target Schemas.

For example, there is a fixed Area named “Total”. The Area that precedes it is the Unbounded “ReturnItems”. By setting the “Total” to be offset from “ReturnItems”, and assigning a Row count of 3, Clarify now recognizes that “Total” can be read from/written to 3 Rows from when the last Rows in the “ReturnItems” Area stops.

This can be seen in our sample data below. The two Areas are highlighted, The two Rows that comprise “Returns” has a blue box surrounding it; the “Total” Area begins on the third Row from the last Row of “Returns”.

 

Defining Properties at the Row Group Level

Different properties for a Row Group can be defined using the Properties view. These include
  • Properties tab (Name and Description)
  • Group Constraints tab (Defines constraints for the actual Row Group)
  • Data Group tab (Currently not supported)

Defining Properties at the Cell Level

Different properties for a Cell can be defined using the Properties view. These include:
  • Description (Name and Description)
  • Cell (Row, Column, Type, Default Value on Target
    • Row: Which Row of an Area the Cell belongs in
    • Column: The column the Cell belongs in
    • Type (data): Choices for type include String, DateTime, Boolean, and Number. Type values will be defined as String by default, unless set from within the editor of the new Schema.
    • Default Value On Target: The default value that is used to populate a field when the source contains null data
Note: If you want to use the Default Value On Target feature, you must also configure the Ruleset's Runtime tab's Null Source Action field to UseSchemaOrTypeDefaultValue. See the Runtime topic for more information.

Related articles

Comments

0 comments

Please sign in to leave a comment.