Sign in
< Back to User Guide Top

Creating Spreadsheet Schemas using Sample Data (template file)

Create a Schema using sample spreadsheet data. The sample data, stored as a template file, can be reused to build and maintain multiple Spreadsheet Schemas. Updating the template file won’t require you to re-build Schemas - they can be updated automatically.

Notes:
  • Template files are for formatting only; they are not required as part of an outbound spreadsheet integration.
    Note: One exception is when writing out DateTime values. If no template exists, this data type will be written out in the numeric representation, instead of the intended value.
  • Always verify the sample spreadsheet before using it as the template file. For example, if adding a row into an unbounded area, make sure formulas still work, etc.
  • While you also have the option to create a Spreadsheet Schema from scratch (i.e., creating and defining each area and cell), Cleo recommends using the template file to build the Schema.
  1. Select File | New | Spreadsheet Schema from the main menu (or use right-click options: New|Other|Schemas).
    Note: If you select the Package in which the Spreadsheet Schema should reside before creating it, some parameters are automatically populated in the New Spreadsheet Schema window.
  2. Select the Schema version (this relates to the old or new transformation engine). Version 2 is the default selection. If creating a Version 1 Schema, refer to Creating Spreadsheet Schemas with Sample Data (for Version 1 Rulesets only)
  3. Choose the Create From Sample radio button option and select a sample spreadsheet. Since Clarify requires this file to be included in the same folder containing the new Schema, it will automatically create a copy of the same file in the src folder. If you run the Schema wizard again, Clarify gives you the option to create a new version of the sample file, or overwrite the original one in src. It is this file that will be identified as the Template File in the editor.
  4. From the Worksheet panel, select a worksheet to be defined in the Schema. You can select multiple worksheets.
  5. Create and define the area.
    • Provide an area name (required) and an optional description.
    • Set the type:
      • Fixed: Used for data that appears once in a known location. (For example, a Header or Summary.)
      • Unbounded: Used for data that repeats. Only select the first row of the area. For example: a Header or Summary.
  6. Consider which cells contain related data. You will be selecting them in groups according to this relationship.

    • Click and drag or Ctrl+Shift click to select adjacent cells in an Area.

    • Ctrl click to select non-adjacent cells in an Area.

    • Select data cells. Do not select headers or labels.

    An Example: if all the cells that comprise a single line item (Qty,Unit, and Description) are in a single row, you might wish to select each cell in that row and create an area called Line_Item.

  7. Click a cell name to edit and define the data type. Clarify attempts to determine the exact data type for the cell based on the data present. If no data exists (null) then it will default to String. Types include:
     
    • DateTime
    • Number
    • Boolean
    • String (default)
    Note: If not defining data type here, you can do so later from within the Schema editor. See Customizing Spreadsheet Schemas (v2).
  8. For an unbounded area, use the Create Row Expression panel to determine how far Clarify should continue to read within an area. Clarify stops reading and ends the area once the criteria is not met. Select the column for inspection and set the condition. Options are:
    • NOTEMPTY: checks to see if the cell is Not Null, Not a Blank Cell, and if it is a String Cell, checks if it is not all white space.
    • STARTSWITH: requires text in next parameter
    • ENDSWITH: requires text in next parameter
    • CONTAINS: requires text in next parameter
    • ISTYPE STRING
    • ISTYPE DECIMAL
    • > requires numerical text in next parameter
    • < requires numerical text in next parameter
    • >= requires numerical text in next parameter
    • <= requires text in next parameter
    • < > (not equal to) - requires numerical text in next parameter
    • = requires numerical text in next parameter

    If the condition requires a text or numeric text, simply type it in the field to the right.

    Click Add Phrase to apply it to the condition, which displays in the field below the expressions.

    For multiple criteria, use the AND, OR, (, ), or NOT expressions to add to the first phrase.

     

    In the screen shot above, the row expression sets Clarify to continue reading an area if Column B is not empty. This was done by:
    • Choosing column B
    • Selecting NOTEMPTY
    • clicking Add Phrase. This created the phrase: ( col[B] NOTEMPTY ).
  9. Click Next to proceed to the Area panel.
  10. At this point in the Wizard, you can finish the Schema or create another area. If using multiple worksheets, Areas must be created for each. To continue, select Create Another Area to repeat the process; If done, select No More Areas to finish.
    When complete, the Schema editor appears.
Next: Use the Schema editor to make additional changes. More information can be found in Customizing Spreadsheet Schemas (v2).

To use the Schema, select it when creating Rulesets.

Related articles

Comments

0 comments

Please sign in to leave a comment.