Excel Format
The following sample Excel data import file shows that the first row is a header, and the remaining rows are import data. Exported Excel files always include a header row, though this is optional for imports.
It is recommended that users use the newest supportable version of Excel. File errors may occur during different STEP processes if using a template set to an older Excel version. For more information on supported versions of Excel, refer to the current Platform and Software Support documentation in the System Update and Patch Notes.
Keep the following points in mind when working with STEP data using the Excel format:
-
The following node types / super types can be imported and exported via Excel format: products, classifications, entities, assets (objects, not content), and attributes (objects and definitions).
-
A Excel import or export file will include data in the same arrangement as relational database tables. This means that each object is displayed as a single row in the file and each object property item is displayed as a single column.
-
References and/or data containers can be exported in this format where multiple values are separated by a delimiter. References are separated by semicolons, while data containers use a common prefix (#) for attribute columns. For a more easily readable file layout, refer to the options for Insert References / Data Containers on the Outbound Map Data - Data Source topic and the options for Inserted References / Data Containers on the Inbound Map Data - Map topic.
-
Imports and exports are context and workspace specific. By default, data is imported to or extracted from the context and workspace in use when the process is started.
-
When planning to import data back into STEP, include STEP ID in the export.
-
There is a significant risk of importing orphan data when using empty value Excel or CSV exports across categories and/or object types and re-importing them when individual attributes are mapped versus using attribute groups (where invalid and non-linked attributes in the group(s) are excluded).
Important: Excel binary files, those with an XLSB extension, are not supported during import.
Format Availability
Excel is available for selection in:
-
IIEP - refer to Creating an Inbound Integration Endpoint
-
Import Manager - refer to Creating a Data Import
-
Export Manager - refer to Creating a Data Export
-
OIEP - refer to Creating an Outbound Integration Endpoint
Mapping
This format requires creating a data map between STEP and the data being processed, and may also include data transformations. For details, refer to Data Mapping.
Inbound Data
Excel import allows creation of and updates to products, classifications, entities, attribute values, data container values, and references. However, system setup objects (for example, LOVs, users, reference types, and so on), cannot be created via import.
Because the Map Data process allows selection of only a single node type, only one node type / super type (products, entities, etc.) can be imported at a time. When multiple super types exist in the same import file, a separate import is required to successfully import each type of object, starting with classification data, then product data, and finally, entity data. When the inbound file includes data for node types other than the one selected, two things may happen: 1) assuming none of the data prevents the import, new objects are created using the supplied information and the selected super type, 2) the execution report details the skipped records when included data, like parent ID, is not found in the selected super type hierarchy. Alternately, split the inbound data file by super type and process accordingly.
Note the following specifications about importing Excel data:
-
Unicode is used as the character set for importing Excel data files.
-
Formulas are not allowed in the XLS or XLSX file. Data columns that include calculations or formulas must be loaded as fixed values.
-
Excel binary files, those with an XLSB extension, are not supported during import.
-
Transformations in the import mapping can also be used to concatenate using the 'Append from' transformation.
-
When loading data that includes special characters (such as trademark ™, registered trademark ®, or mathematical symbols), ensure that the load file conforms to the Unicode character set.
-
Ensure that data originally entered as a fraction (e.g., 3/16) was not converted to a date by Excel.
-
It is strongly recommended that all Excel cells are set to 'Text' format.
-
It is recommended to remove any formatting applied to numeric values in Excel. This allows the Excel value to be entered into STEP, not the value that Excel transforms it into for display purposes. However, setting the inbound parameter 'Use date and number formatting from sheet' to 'yes' will cause the values to be imported as-is from the sheet. Refer to the 'Inbound Parameters' subsection below for more information.
-
Enter attribute values and units separated by a space in the same cell, with the units following the values. STEP separates the values from the units and validates them individually. If your attribute values and units are in separate columns, you can either use the concatenation method in Excel or use a database program to combine them.
Important: Only the first sheet in the workbook is available to import. If importing a workbook with multiple sheets, move the sheet with data into the first position.
When using Excel to import a reference type that allows multiple references, several elements affect the data added to STEP, as specified in the Importing Multi-Valued References or Links with Excel topic.
To use Excel for importing calculated attribute value templates, refer to the Importing Calculated Attribute Value Templates with Excel topic.
Excel can be used to modify or create multiple attributes at the same time within System Setup. For details, refer to the Managing Attribute Parameters with Excel topic.
Deleting Values During Import
Removing a value requires the [delete] (including the brackets) text in your load file and that the object is mapped. Unlike other import formats, empty Excel cells are ignored and do not cause a value to be deleted. If the value being deleted was inherited, the result is not a blank field, but the inherited value is restored.
This functionality differs from imports using STEPXML or CSV files.
Inbound Parameters
The following parameters are available in both Import Manager and IIEP:
-
Conversion Preview - a sample of the first few lines of the file is displayed to allow verification that the selected options are correct.
-
Has Header - select if the file has a header line. If the first line (row) of the Excel file has header information, such as attribute names that match STEP, the Auto Map feature is available to map the columns of data to the appropriate object in STEP. Although a header row is not required, without it the user must be able to identify the data for manual mapping.
-
Trim whitespace - select 'yes' to remove leading and trailing spaces in values or select 'no' to leave them.
-
Use date and number formatting from sheet - by default, this is set to 'no.' The 'no' setting allows for numeric values to be automatically imported in a standardized, US-English based format, and for date values to be automatically imported in ISO format, instead of the regional formats that may appear in source spreadsheets. For details and examples using this parameter, refer to the Date and Number Formatting with Excel topic.
Import Manager
IIEP
Outbound Data
When data leaves STEP via Excel format, the UTF-8 character set is used to export the data.
Note: When exporting object type, the default selection on the mapping step is 'Object Type Name.' However, when importing the same file, the auto-mapping feature expects the 'Object Type ID.' To generate an export file that is prepared for reimport, use the ID Aspect to map object type. For more information, refer to the Aspect - Transform Outbound topic.
Refer to the Attributes (and Data Containers) - Data Source Outbound topic for information on what is included in the output file based on this mapping option.
Export Manager
The following parameters are available when choosing the format 'Excel' in the Export Manager:
-
Excel Version - Excel 2007, which has a limit of 1,048,576 rows.
-
Export Empty Fields - determines how fields without values are exported. To output calculated attribute values, you must also enable the 'Include Calculated Attribute Values' checkbox on the Advanced step of Export Manager or OIEP tab.
-
No - exports only fields that include a value.
-
Yes - values (attributes, references, metadata, data containers, etc.,) that are valid and linked will be included in the export even when no objects are populated. Attributes that are not valid / linked are extended when an attribute group is used in the mapping.
OIEP