Data Management Capabilities > Managing Part Structures > Windchill Options and Variants Capabilities > Creating and Managing Variants > Variant Specification Information Page > Loading Variant Specifications > Loading Variant Specifications from a Spreadsheet
  
Loading Variant Specifications from a Spreadsheet
You can load multiple variant specifications from a spreadsheet using a Windchill command-line utility. This utility relies on the API that is used to programmatically load variants specifications.
* 
To use this capability:
The Platform Structures module must be installed.
You must be a Site Administrator and a member of the Platform Structures group.
To load variant specifications from a spreadsheet:
1. Create a spreadsheet using a template VariantSpecLoader1.xlsx available in WT_HOME\loadFiles\ato\testdata\variantSpecLoader.
2. Run the following Windchill command-line utility:
Windchill com.ptc.windchill.option.variantspec.loader.VariantSpecLoader -d <My_Spreadsheet_Path>\<My_Spreadsheet>.xlsx -u <my_user> -p <my_password>
3. Review the output file to resolve any errors. The output file is located under WT_HOME\logs.
The spreadsheet contains 3 worksheets where you enter information that relates to the variant specifications and the part structure configuration:
Part Configuration Specification worksheet (Part Config Specs)— Information for this worksheet is similar to the information you specify on the Configuration Specification tab of the Configure window when you configure a part. This worksheet has 5 fields: Configuration Specification Name, View, Life Cycle State , Baseline, and Effectivity Date. The Name is required. Values for View must be valid entries corresponding to the View field on the Configure window. The values you enter are validated by the system.
Option Filter Configuration Specification worksheet (Option Filter Config Specs)–This worksheet defines the configuration specifications for the Option Filter. This worksheet has 3 fields: Config Spec Name, Life Cycle State, and Effectivity Date. The information you enter is similar to what you specify in the Set Configuration Specification for Option Filter window that you access from the Option Filter window by clicking the icon. The values you enter are validated by the system.
Navigation Criteria worksheet (Navigation Criteria)– Provide information on the choice rules and selected choices to capture the selections in the Option Filter. The worksheet has the following fields:
Variant Specification section:
Name–Enter the name to assign to the variant specification.
Number–Use this column only when you want to reconfigure an existing variant specification. If a valid number is provided, and the variant specification with this number exists, the persisted name is used.
* 
Either Name and Number must be provided, not both.
Description – Provide the description for the variant specification (up to 2000 characters).
Location–Specify the location where to store the variant specification. If this field is empty, the utility uses the default location. If this field is empty for the variant specification creation, the utility issues an error.
Create Variants–This is a Boolean value. When set to true, the system creates module variants for the variant specification. Because the creation of module variants is asynchronous, the utility only notes the success/failure of the creation of the queue entry for each variant and not the actual variant. When this field is set to false, the utility only creates the variant specification.
Part section–This section provides details of the part to configure using the variant specification. The utility expects that a part has an assigned option set. The row is not processed if there is no assigned option set.
Number–Number of the part (for example, Bicycle1).
Revision–Revision of the part (for example, A).
Iteration–Iteration of the part (for example, 1). In this example, the part is Bicycle1 A.1.
* 
If you provide information for the part, then the information about the option set is redundant. You must provide information either for the Part or Option Set field, not both.
OptionSet section—The variant specification is created using an option set. Such variant specification is created with the utility if a part was not specified for that row.
Name—Name of the option set.
Revision—Revision of the part (for example, A).
Iteration—Iteration of the part (for example, 1).
Navigation Criteria section —This section captures the information specified on the Configuration Specification tab and the Option Filter tab of the user interface:
Apply to Top—Setting this attribute to true is equivalent to checking the same box on the Configuration Specification tab on the Configure window. When Apply to Top is set to true, the system applies the configuration specification to the top-most part on the part structure provided.
* 
This information is not necessary for variant specifications that are created using only option sets. If this information is provided, the validator reports an error.
Part Configuration Specification—Provide a list of part configuration specifications that have already been loaded using the worksheet 1. You can provide multiple configuration specifications by separating them with a coma (,). If a part was specified in the column F-H, and this column is left blank, the utility loads the Latest part configuration specification and uses it to create the variant specification.
Option Filter Configuration Specification—Provide a list of the Option Filter configuration specifications that have already been loaded using the worksheet 2. You can provide multiple configuration specifications by separating them with a coma (,). When this information is left blank, the utility loads the latest Option Filter configuration specification and uses it to create the variant specification.
Disable Rule Checking—By default, this is set to false. When set to true, it is the equivalent of checking the box on the Configure window.
Filter Mode—This is similar to the drop-down list on Option Filter tab (where you select Standard or Alternate).
Start Options column—The empty column represents the starting of the definition of choices to select for the Option Filter.
Additional columns—Provide one column per option whose choices you want to select for the Option Filter.
For example: if you want to select ChoiceA, ChoiceB, and ChoiceC from OptionA, and ChoiceD from Option B, you need to create two columns following the Start Options column—OptionA and OptionB. On the row where you create the variant specification information, enter a coma-separated list of ChoiceA, ChoiceB, and ChoiceC under OptionA, and ChoiceD under OptionB. If the next row (that is, the next variant specification) has choices selected from a different option, create a new column with the name of the option and the respective choices in the cell under it for the row you are filling out. You can leave the cells under OptionA and OptionB blank for this row.
* 
If a version of the same variant specification exists in the system, you need to refer to the variant specification by its number.
End Options—This is the last column on the worksheet. It marks the end of all option and choice selections for the Option Filter.
Output File
When you run the utility to import a spreadsheet, the system creates an output file. The output files are located in the log folder WT_HOME\logs and are named VariantSpecLoaderReport_<random_generated_number>*.
The output file contains the following information:
The row number on navigation criteria worksheet that is being processed.
The operation status: SUCCESS or FAILED.
The variant specification number (if the variant specification was successfully created).
CREATED—If a new variant specification was created, CREATED is set to true. Otherwise, CREATED is set to false.
UPDATED—If the variant specification is an existing one, then UPDATED is set to true. This means that the variant specification was reconfigured based on the variant specification number provided in the spreadsheet.
ERROR—Reports errors during the validation while processing the spreadsheet or errors encountered while creating objects. If an error is encountered, the utility does not process that row, records the error in the report, and moves on to the next row.
WARNING—If warnings or assumptions were made while processing the row, they are logged as warnings on the report.
Guidelines for Using a Spreadsheet for Loading Variant Specifications
Consider the following information:
Use only .xlsx (default Microsoft office Excel 2007) spreadsheet format to load variant specifications.
Note that in UNIX, a windchill command to load variant specfications is case-sensitive. Use the following syntax:
windchill com.ptc.windchill.option.variantspec.loader.VariantSpecLoader -d <My_spreadsheetpath>  
When you reconfigure a variant specification using a spreadsheet, entering Create Variants = True, generates a new variant (by overriding an existing variant) even if there is no change in the structure and choice selections for the variant specification.
You cannot reconfigure a variant specification that was created using an option set.
Creation of the variant specification using a user-defined variant specification number is not supported using the out-of-the-box API or spreadsheet.
To provide the folder location for the variant specification in the spreadsheet, you can use all folders created in the /Default cabinet of that product, for example, /Default/ABC.
To reconfigure an existing variant specification, you must specify the part with which the variant specification is associated. Do not specify an option set in such cases.
Reconfiguring the variant specification based on its version is not supported. The latest version is always reconfigured.
Follow these guidelines for special characters:
Choices with double quotes in their names must be preceded with a backslash “\”, for example, “\a”1\”.
Choices with special characters in their names must be included in double quotes, for example, “a_1”.
Spreadsheet Validation
The following validations are performed when a spreadsheet is being processed:
You can specify either the part or the option set, not both.
The part must have an assigned option set.
The part must exist.
The option set must exist.
The folder must exist and must be specified when creating a new variant specification.
The specified part configuration specification must exist.
The specified Option Filter configuration specification must exist.
You can specify either the variant specification name or number, not both.
The variant specification with the specified number must exist.
To reconfigure an existing variant specification, you must specify the part with which the variant specification is associated. Do not specify an option set in such cases.
You must provide either the part or the option set.
Running the Utility in the Debug Mode
You can run the utility in the debug mode that creates a log4j file in the wt_home\logs folder that you can use for debugging errors. The log file records errors for each row of the spreadsheet that is being processed.
To use the utility in the debug mode, use the following syntax:
Windchill --jap=wt.properties?com.ptc.windchill.option.variantload.java.args com.ptc.windchill.option.variantspec.loader.VariantSpecLoader -d <My_Spreadsheet_Path>\<My_Spreadsheet>.xlsx -u <my_user> -p <my_password>