You are here: Data Exchange > Data Formats > Excel Format

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.

The available version for Excel within STEP are:

Excel 97/2003 - limit 65,536 rows
Excel 2007 - limit 1,048,576 rows

Keep the following points in mind when working with STEP data using 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.

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 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.
When loading data that include 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.
All Excel cells must be set to 'Text' format.
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.
To remove an attribute value, use the entry [delete] in your load file. Empty cells do not cause values to be deleted. If the value being deleted was inherited, the result is not a blank field, but the inherited value is restored.
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.

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.

Import Manager

IIEP

Outbound Data

When data leaves STEP via Excel format, the UTF-8 character set is used to export the data.

Export Manager

OIEP

2017, Stibo Systems