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.
-
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.
-
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).
-
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.
-
From the Worksheet panel, select a worksheet to be defined in the
Schema. You can select multiple worksheets.
-
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.
-
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.
-
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)
-
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 ).
-
Click Next to proceed to the Area panel.
-
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 .