Sign in
< Back to User Guide Top

Creating Spreadsheet Schemas with Sample Data (for Version 1 Rulesets only)

Create a Spreadsheet Schema using sample data in order to have Clarify correctly format data when being read from or written to a spreadsheet. Note: this Schema is not supported in the latest transformation engine (Version 2).

You must have a sample spreadsheet in Clarify to use the Create from Sample option. Cleo recommends creating a new folder called samples in your Project at the same level as the srcand META-INF folders to hold sample data.
You can also create a Spreadsheet Schema from scratch, creating and detailing each area and cell. However, Cleo recommends using a sample file to simplify the process.
  1. Select File | New | Spreadsheet Schema from the main menu.
    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. The New Spreadsheet Schema window appears. Choose Create From Sample from the Create radio button options. Click Next.
  3. Either type a file path in the File Name field or click Browse to produce the Select File window. Once you've selected a file, click Next.
  4. The Worksheet panel appears. Select which of the spreadsheet's worksheet(s) you want to define and click Next.
  5. The Create Area panel appears.
    To define the cells in this step, consider which cells contain related data. You will be selecting them in groups according to this relationship. For example, if all the cells that comprise a single line item (SKU, item description, quantity, and cost) are in a single row, select each cell in that row to create an area called LineItem.

    Here is a sample of what areas may look like in a purchase order:

  6. In the worksheet's table, select the cells that contain related data for a single Area. Click and drag or Ctrl+Shift click to select adjacent cells in an Area. Ctrl click to select non-adjacent cells in an Area.
    Note: Select only the data cells. Do not select headers or labels.
  7. With the cells selected, type values for the (mandatory) Name parameter and (optional) Description parameter.
  8. Choose a Type radio button. Options are:
    • 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, line items/item details.)
  9. Click Next. The Cell Names panel appears. Click each proposed cell name to make sure it corresponds to the correct data. If you want to change a cell name, click the name, type a new one, and press Enter.
  10. If the area is Unbounded, the Create Row Expression panel appears. The phrase you create defines when Clarify should continue reading or writing data for this area. When the criteria is not met, Clarify stops reading or writing, and ends the area.
    1. In the first Column drop-down, choose the letter of the column that Clarify should inspect.
    2. In the second Column drop-down, choose a condition. Options are:
      • NOTEMPTYchecks if it 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
      • ISTYPESTRING
      • ISTYPEDECIMEL
      • > requires text in next parameter
      • < requires text in next parameter
      • >= requires text in next parameter
      • <= requires text in next parameter
      • < > (not equal to) - requires text in next parameter
      • = requires text in next parameter
    3. If the condition requires it, type the text or number to apply to the condition.
    4. Click Add Phrase to create a phrase from the conditions, which will appear in the area below.
    For example, if you want Clarify to continue reading or writing if Column B is not empty, choose B and NOTEMPTY, then click Add Phrase. Clarify creates the phrase: ( col[B] NOTEMPTY ).
    Note: If you have multiple criteria, after adding your first phrase click AND, OR, (, or ) and define another phrase.
  11. Click Next. The Area panel appears, asking Do you want to create another area?
    • If you have not created an Area to account for each group of data, select Create Another Area and click Next. Complete steps a through e for each Area.
    • If you have created all necessary Areas, select No More Areas and click Next.
    Note: If you are using multiple worksheets, remember to create Areas for each.
  12. The Spreadsheet Schema panel appears. Provide the Source folder, Package, and Name.
  13. Click Finish. An editor appears.
Next: Make any changes to your Spreadsheet Schema in its editor. If you need help customizing it, see Customizing Spreadsheet Schemas (v1).

To use the Schema, select it when Creating Rulesets.

Also, when creating a Business Process, if you are writing your target data to a spreadsheet, be sure to use the ConvertSpreadsheet task in the Business Process after the transformation and before writing. See A complete list of Business Process Tasks for more information.

Related articles

Comments

0 comments

Please sign in to leave a comment.