Customizing Spreadsheet Schemas

Use the Spreadsheet Schema editor to format data and determine how the Integration engine 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

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.

Note on Worksheet Binding (matching incoming data to Schema)

Spreadsheet Schema behavior supports an alternative to the typical matching of incoming data being received with spreadsheets. The matching, or binding, will still be based on the Worksheet Name field, however, if there is no match, then binding attempts will be by index. This reflects the order: Worksheet Name, then index – with no mixing of the two. This provides better flexibility as Customers don’t have to rely on actual static worksheet names.

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” have a blue box surrounding them; 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.