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.
|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|
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
- 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
- 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
- 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
- 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