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.
The available export versions for Excel within STEP are as follows. However, versions of Excel newer than 2007 can be used for imports, and selecting 2007 for an export still means that the exported data will be readable by a later version of Excel.
- Excel 2007 - limit 1,048,576 rows
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, see the current Platform and Software Support documentation in the System Release 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.
- 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.
Important: Excel binary files, those with an XLSB extension, are not supported during import.
Format Availability
Excel is available for selection in:
- IIEP - see Creating an Inbound Integration Endpoint here
- Import Manager - see Creating a Data Import here
- Export Manager - see Creating a Data Export here
- OIEP - see Creating an Outbound Integration Endpoint here
Mapping
This format requires creating a data map between STEP and the data being processed, and may also include data transformations. For details, see Data Mapping here.
Inbound Data
Excel import allows creation of and updates to products, classifications, entities, attribute values and references. However, system setup objects (for example, attributes, 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.
- 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. See the 'Inbound Parameters' subsection below for more information.
- Enter attribute values and units 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.
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 within this guide here.
Excel can be used to modify or create multiple attributes at the same time within System Setup. For details, see the Managing Attribute Parameters with Excel topic here.
Deleting Values During Import
Removing a value requires the use of the entry [delete] (including the brackets) 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, see the Date and Number Formatting with Excel topic here.
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, see the Aspect - Transform Outbound section of the Data Mapping documentation here.
Export Manager
OIEP
