CSV specification
All import operations require that the entity to be imported can be identified uniquely. For that purpose all formats define a column named UniqueName.
During import, by default unless otherwise specified the system will create an entity with the given unique name if it doesn't already exist.
Import operations are executed as online single-pass operations. This means that rows that reference other rows in the same file must appear after the referenced row(s) in order to avoid failure due to unknown values.
The import engine reserves the special keyword NULL (all uppercase). The word is treated differently, depending on the import mode that the importer executes in. See details in Import mode.
Import mode
CSV import engine supports two import modes:
- Merge (default)
- Overwrite
As Merge mode is default, all import endpoints support Merge mode with all columns supported.
It is not guaranteed that all endpoints support Overwrite mode.
Additionally, it is not guaranteed that all columns supported in Merge mode are supported in Overwrite mode.
For additional details, see the documentation of the individual endpoints below.
Merge mode
In Merge mode if not otherwise stated, the NULL keyword is treated as if the column was not present for the given import row.
Some columns support assigning an empty string as row value which will be treated as an explicit assignment of an empty value. This is documented for each column for which it is supported (note that many columns do not support empty string values).
Merge mode import generally merges new data with existing data, which means that if some object property is not represented by a column header, or if a row value for an item property is NULL, that property is not changed. Exact behaviour depends on entity type and column type. See documentation below for detailed information.
When no import mode is explicitly mentioned, Merge mode is implied.
Overwrite mode
In Overwrite mode, the NULL keyword is treated as assigning a NULL value for the item property that is represented by the column header.
When item properties are represented by multiple columns in a CSV file (eg. lists of values, attribute assignment values, etc.), including one of these columns implies that the entire property is changed. This, for example, implies that when changing a list property such as the AdditionalCategoryPlacement_n columns, all additional category placements will be overwritten with whatever is in the import CSV file (if a product had five additional category placements, and a CSV file only specifies one additional category placement, the importer will make sure that the product only has the one additional category placement defined in the CSV file).
Warning
Only properties of an item that are (partially) represented by a column in the import file are considered as part of the single source of truth. Item properties not (partially) represented by any column in the import file are not overwritten or deleted.
See detailed documentation for further information.
Overwrite mode support
Overwrite import mode is supported for a subset entities and for a subset of supported columns.
Currently supported entities for Overwrite mode are:
- Product import
Products
A row for a product in the import set will be considered a partial update of the product.
When a product is encountered in an import file, the product will be created if a product doesn't already exist with the same name. Following this, any attributes which are present as columns in the import file will be attached and populated on the product.
The first column of the file must have the name UniqueName and must contain the unique system name of the products.
Note
Import of a product will never attempt to create and guess types of attributes. The attributes referenced in the import set must all have been created and configured before starting the import, and referencing attributes which cannot be resolved by their system names will result in a validation error and a failed import.
Overwrite support
Only the following columns are supported in Overwrite mode:
UniqueNamePrimaryPlacementCategoryAdditionalCategoryPlacement_n- Attribute-oriented columns supported by Overwrite as defined in Attribute assignment columns
Product specific columns
The product import CSV format supports all attribute-oriented columns as defined in Attribute assignment columns.
Apart from this, columns are supported enabling the operations explained below.
Product placement
The primary placement of a product can be included in the import operation by adding a column with the header PrimaryPlacementCategory with the column cells containing the system name of the category the product should be placed in.
For additional placements apart from the primary, columns AdditionalCategoryPlacement_1, AdditionalCategoryPlacement_2 ... AdditionalCategoryPlacement_n can be included.
The importMode chosen in the import endpoint determines whether PIM will either overwrite or merge with existing information. If the endpoint does not have an explicit importMode parameter, Merge is used.
Merge
If PrimaryPlacementCategory is included, empty values will result in any existing placement being removed, whereas rows with the value NULL in the cell will leave any existing placement for the row product untouched. So, given:
Will cause any pimary category to be removed from product, whereas:
Will leave primary category untouched on the product.
After an import containing AdditionalCategoryPlacement_n columns have completed, a given product's placements will be the union of existing placements and those placements indicated by placement columns. So, consider a product which is already placed in CATEGORY_1:
The resulting PRODUCT-1 will be placed in CATEGORY_1, CATEGORY_2, and CATEGORY_3.
Changing a placement from primary to secondary can be done simply by setting it as additional placement. For example consider a product that is currently placed in CATEGORY_1 as primary, the following CSV will convert it to a secondary placement:
Going the opposite way, changing a secondary placement to a primary, can be done in a similar way. Consider a product that is currently placed in CATEGORY_1 (as secondary). The following CSV will convert it to a primary placement:
Warning
Merge mode does not support the removal of secondary placements from a product using the import API. Use overwrite mode for that instead.
Overwrite
When PrimaryPlacementCategory is present in the CSV, the product will be placed in the specified category and it will be marked as primary. Specifying NULL will remove an existing primary category placement.
When at least one AdditionalCategoryPlacement_n (n must be consecutive and start with 1) column is present, all existing additional placements will be replaced with what is assigned in the CSV.
So, given:
The resulting PRODUCT-1 will be placed in CATEGORY_1 (as primary), CATEGORY_2, CATEGORY_3, and CATEGORY_4 regardless of which placements existed before the import.
Specifying NULL for AdditionalCategoryPlacement_n means remove placement. For example consider this:
The resulting PRODUCT-1 from the previous example will end up being placed in CATEGORY_1 (untouched primary category) and CATEGORY_2 while removing CATEGORY_3 and CATEGORY_4.
Removing all non-primary placements can be done with:
Removing both primary and non-primary placements can be done with:
Putting it all together let's have a look at one final example:
As a result of this import file, the following changes will be made:
PRODUCT-1 is placed in CATEGORY_1. Any existing primary and secondary category placements will be removed, PRODUCT-2 is placed in CATEGORY_2 (as primary), CATEGORY_3, and CATEGORY_4, and PRODUCT-3 is unplaced from its primary category and secondary category placements.
External IDs
External IDs can be manipulated by including one or both of the columns ExternalIntId and ExternalStringId in the import file as shown in the following example file.
Warning
It is currently not possible to completely remove the external IDs of a product using the import API.
For ExternalIntId, both empty values and NULL values will result in the existing value remaining unchanged.
For ExternalStringId, NULL will result in the existing value remaining unchanged whereas an empty value will assign an empty external string ID to the product.
Master/variants
Master/variant associations can be assigned by making use of the VariantOfMaster column. If used, the column should contain the unique-name of the master to add the current product to as a variant.
Leaving the column blank will result in the product being removed as a variant if it had such an attachment already, If the cell is assigned the value NULL, any existing master/variant association for the product will be left untouched.
Example of use:
In this example, the product 'Dumbbell 5kg' will be cleared of any existing master association, the product 'Dumbbell 5kg var 1' will be assigned as a variant of 'Dumbbell 5kg' and 'Dumbbell 5kg var 2' will be assigned as a variant of 'Dumbbell 5kg var 1'.
Note
If the master/variant structure is changed using the import API, due to the fact that the import engine parses and processes the file in a single pass it is a requirement that the master/variant structure is sorted by tree order.
This is to say that the row for a master should always be present before the row for the variant in the import file.
The master/variant graph will be validated for cyclical structures during the import, so it may be necessary to include a row to un-assign a product as a variant of a master, and a secondary line for the same product associating it with another master.
There is no requirement that the master of a product must be included in the file if it has already been created through either a previous import operation, or manually through the UI.
If the VariantOfMaster column is included, a value of NULL will result in no change. An empty value will result in clearing the master attachment of the product, if it had one previously.
Variant ordering
Variants of a master are considered to have an order. It is possible to manipulate this order via the CSV import with the PlaceVariantOfMasterBefore column. The column should contain the unique name of the variant before which the imported variant is to be placed.
If an empty but not NULL system name is given to place before, it will be interpreted as a request to place the imported variant at the end of the list.
It is possible to combine the VariantOfMaster column with the PlaceVariantOfMasterBefore column so that the product in question will be placed as a variant of the master defined in VariantOfMaster column before the existing variant of that master, defined in PlaceVariantOfMasterBefore column. It is also possible to use the PlaceVariantOfMasterBefore column without the VariantOfMaster column.
Example of use:
In this example we assume that both products Dumbbell 5kg var 1 and Dumbbell 5kg var 2 are variants of the same master - say Dumbbell 5kg. After running the import, we have ensured that Dumbbell 5kg var 1 is the variant just before Dumbbell 5kg var 2 in the list of variants of Dumbbell 5kg.
In the same example, if Dumbbell 5kg var 2 and Dumbbell 5kg var 1 are not variants of the same master, the importer would throw an error.
Processing of VariantOfMaster and PlaceVariantOfMasterBefore is done in the order that the products appear in the CSV file.
Example: Assume that Dumbbell 5kg var 1 and Dumbbell 5kg var 2 are both variants of Dumbbell 5kg. Consider the CSV:
The above import will fail, since Dumbbell 5kg var 2 is made a variant of Dumbbell 5kg special, before Dumbbell 5kg var 1 is placed before it.
In the following alternative example, the row order is swapped:
By swapping the row order, the importer will first place Dumbbell 5kg var 1 before Dumbbell 5kg var 2 in the variant list of Dumbbell 5kg, before Dumbbell 5kg var 2 is made a variant of Dumbbell 5kg special.
Potential idempotence issues
Due to fact that ordering depends on the state of the products already in PIM, it is possible to construct CSV files that can be imported once but will fail on any consecutive imports. While it is possible to construct the CSV files such that the behavior of the importer is idempotent, itempotence can not be guaranteed in general.
Note
In the current version of the import engine, some optimizations are performed on the updates in order to minimize the number of operations on the underlying database.
If performance is an issue, in order to help the engine with leveraging these optimizations, it may give some throughput improvements to group variants of the same master together in the CSV file.
Optimization is done in such a way that batch sizes do not influence the import behavior.
Brands
If an imported brand does not already exist, it cannot be created without also adding the Name column.
This cannot be checked before the import operation starts, and thus will not be included in the up-front validation when enqueueing an import job and will instead result in a failed import job.
Brand specific columns
The brand import CSV format supports all attribute-oriented columns as defined in Attribute assignment columns.
Apart from this, brand sepcific columns are supported as follows.
Sub-brands
Sub-brand structures can be defined by making use of the SubbrandOf column, as shown below.
In this example, three brands will be created if not already present in the database, with Brand 2 being a sub-brand of Brand 1, and Brand 3 being a sub-brand of Brand 2.
Note
As the import file is consumed in a single-pass operation by the import engine, it is a requirement that the import file is arranged such that the rows for newly created brands which should be assigned a sub-brand are located before the rows of the sub-brands.
It this is not the case, the import job will fail, with an indication that the main brand could not be found.
Brand name
The name of a brand is specified using the column Name.
Since the name of a brand is possible to segment on translation culture, it is possible to indicate the name in different languages using the same format used for segmented values for an attribute column, but restricted to the relevant format for translation cultures only.
The default culture can be indicated by using the column header Name:Default, or by not indicating a translation culture for the column.
Note
Including both a Name and a Name:Default column will result in an ambiguous state, and will fail validation.
The following example creates a brand with the unique name 'ColourMakers', with the name defaulting to 'ColourMakers' and specifically translated as 'ColorMakers' for the US-english market, and 'Farvefabrikken' for the danish.
Bundle items
Importing items for a bundle requires the bundle as well as the items to have been created already, whether through the product import API, or directly through the UI.
If either bundle or item products don't already exist, the import operation will fail.
Bundle item specific columns
The bundle import CSV format supports all attribute oriented columns as defined in Attribute assignment columns.
Apart from this, bundle item specific columns are supported as follows.
Item name
The product to be added to the bundle is specified using the ItemUniqueName column using the unique system name of the product being added.
Operation
The operation to perform on the bundle item is specified using the ItemOperation column.
Legal values are AddOrUpdate and Remove.
In the above example, the product 'PRODUCT_002' will be removed from the bundle configuration of PRODUCT_001. Additionally, the product 'PRODUCT_003' will be attached to the PRODUCT_001 bundle, the attribute 'BUNDLENAME' will be assigned to the bundle attachment, and te value 'Gratis is' will be assigned to the attribute.
If the ItemOperation column is not included, the import engine will default to AddOrUpdate behaviour.
Note
The description of the bundle -> bundle item relation is specified using the Note column. This column is required, with an empty value resulting in any existing note being removed from the attachment.
The inclusion of the Note column, and check that all values are valid, is handled during the pre-import validation and will give immediate feedback before the import job is enqueued.
In the above example, the note for PRODUCT_002's inclusion into bundle PRODUCT_001 will be overwritten with the value 'Internt notat'.
Amount
The number of product instances included in the bundle can be configured through the use of the Amount column.
Note
If the Amount column is included, the content of the cells must either be a legal decimal number (see Format requirements), or the value NULL.
If the value NULL is specified, and the item is already included in the bundle, the existing amount will not be altered. If the product was not already included in the bundle, it will be interpreted as an amount of 1.
In this example, the number of PRODUCT_002 will be unaltered, whilst the number of PRODUCT_003 will be overwritten with the value 1.4.
Global list items
Importing global list items requires that the global list has already been created using the UI.
If the global list cannot be found, the import job will fail.
Global list item specific columns
The global list item import CSV format supports all attribute oriented columns as defined in Attribute assignment columns.
Apart from this, global list item sepcific columns are supported as follows.
Item index
The absolute placement of a global list item can be indicated using the column ItemIndex. The value must be a positive integer, and is 0-indexed.
Operation
The operation to perform on the global list item is specified using the ItemOperation column.
Legal values are Add, Update and Remove.
In this example, the item at index 1 will be removed from the global list 'HEIGHT_VALUES' whereupon a new element is added to the list in the same position, and with the attribute 'UNIT' added with the value 'mm' .
If the ItemOperation column is not included in the import file, each row will be interpreted as an Add command.
Value
The value of a global list item is assigned using the Value column.
In this example, an element will be inserted on index 1 in the global list HEIGHT_VALUES with the value 48.
Since the value of a global list item may be segmented on one-or-more segmentation dimensions, it is possible to indicate the value in different segments using the same format used for segmented values for an attribute column.
If default values are permissible for the given global list, the default value can be indicated by using the column header Name:Default, or by not indicating a segmentation for the column.
Note
Including both a non-segmented Value column and a Value:Default column will result in an ambiguous state, and will fail validation.
Warning
CSV import of multi-value values for a global list item is not currently supported. Only segmented values are permitted.
Predefined values
When importing predefined values for an attribute, it is possible to indicate whether the given item should be added, updated, or removed as a predefined value.
If nothing else is indicated, it will be assumed that the operation should be Add.
Importing predefined values for an attribute requires that the attribute has already been created, either through the attribute import endpoint, or directly in the UI. The predefined value import will make no attempt at creating the attribute if it does not already exist.
Predefined value specific columns
The predefined value import CSV format supports all attribute oriented columns as defined in Attribute assignment columns.
Apart from this, predefined value specific columns are supported as follows.
Item index
The absolute placement of a predefined value can be indicated through the use of the ItemIndex column. The value must be a positive integer, and is 0-indexed.
Operation
The operation to perform on the predefined value is specified using the ItemOperation column.
Legal values are Add, Update and Remove.
In this example, the item on index 1 will be removed from the attribute 'HEIGHTUNIT' whereupon a new element is added in the same position with the value 'mm' for field 1 After this, the field 1 value of the item at index 2 is overwritten with the value 'in'.
If no ItemOperation column is present, it will be assumed that every row indicates an Add operation.
Value
The value of a predefined value item is specified using the columns Predefined_Value_Field_1, Predefined_Value_Field_2, Predefined_Value_Field_3, and Predefined_Value_Field_4, for each of the four possible fields of the attribute.
Since the field values of a predefined value may be segmented on one-or-more segmentation dimensions, it is possible to indicate the value in different segments using the same format used for segmented values for an attribute column.
Warning
There is no current support for multi-value values for predefined value fields.
Product hierarchies
When importing product hierarchies the concept of primary placement and secondary placements are segregated.
The primary placement of a hierarchy is the physical placement of the hierarchy node, whereas the secondary placements are handled as 'links' between the hierarchy and its placement target. The two placement types work independently and are not cross-validated.
Importing product hierarchies will not attempt to automatically create the hierarchy nodes referred through the placement column(s). If the hierarchies are not created when the referring row is reached, the import will fail with a validation error.
Product hierarchy specific columns
The product hierarchy CSV format supports all attribute oriented columns as defined in Attribute assignment columns.
Apart from this, product hierarchy specific columns are supported as follows.
Hierarchy name
The name of the product hierarchy node is specified using the Name column.
Since the name of a product hierarchy node can be segmented on translation culture, it is possible to indicate the name in different languages using the same format used for segmented values for an attribute column, but restricted to the relevant format for translation cultures only.
The default culture can be indicated by using the column header Name:Default, or by not indicating a translation culture for the column.
Warning
Including both a Name and a Name:Default column will result in an ambiguous state, and will fail validation.
Placement
The primary placement of the node can be specified using the PrimaryPlacementCategory.
Warning
The placement of a hierarchy node can only be changed during the creation of the node. If the node already exists, attempting to alter the primary placement of the column will result in an import error.
When importing changes to already existing hierarchy nodes, the PrimaryPlacementCategory value must be set to either the current placement of the node, or the value NULL.
Any secondary placements can be specified by using columns LinkedCategory_1, LinkedCategory_2 ... LinkedCategory_*n *
Note
When importing secondary placements, an empty string is interpreted as a request to remove the placement if the item already exists. To avoid modifications of existing placements, the columns should be given the value NULL.
Attributes
It is important to note that some properties of an attribute can only be set once.
This includes the configuration type of the attribute (Plain, Quantified, Unconstrained etc.), whether the attribute is single- or multi-valued, and the individual configuration (PIM type or global list referencing) of the attribute fields.
Other fields (e.g. the name and description of the attribute, and names of individual fields) can be freely changed.
The system name of the attribute and of the individual fields cannot be altered by way of CSV. If changes are required for these properties, the change must be made through the PIM UI.
Obligatory fields
Since attributes must always have a human-readable name, the Name column must be present when creating new attributes.
Default configuration
When importing a new attribute, a best-effort attempt will be made to fill-in missing configurations with meaningful defaults. This means that an attribute imported by just the system name and name of the description will end up unconfigured, however if the name of field 1 is included in the import file, a Plain configuration, with the associated single field configured as a PIM-typed field with the type PTranslatedStringD will be assumed, and configured as such.
If the default configuration is not suitable, the default values can be overridden using the configuration columns described in the Advanced configuration section below.
If only the attribute configuration is set, the default field types will follow the configuration as follows.
| Configuration | Field 1 type | Field 2 type | Field 3 type | Field 4 type |
|---|---|---|---|---|
| Plain | PTranslatedStringD | N/A | N/A | N/A |
| RectangularArea | PDecimal2 | PDecimal2 | N/A | N/A |
| CuboidVolume | PDecimal2 | PDecimal2 | PDecimal2 | N/A |
| Range | PDecimal2 | PDecimal2 | N/A | N/A |
| Quantified | PTranslatedStringD | PDecimal2 | N/A | N/A |
| Calculated | PString | N/A | N/A | N/A |
| Unconstrained | PTranslatedStringD | PTranslatedStringD | PTranslatedStringD | PTranslatedStringD |
System names
The system name of the attribute being imported is used as a primary identifier and cannot be altered using import/export.
For CSV-imported attributes, the fields will receive the default system names based on the configuration type indicated, as shown in the standard field names section below, and they cannot presently be set or altered by means of CSV import.
Attribute configuration
The type and configuration of the attribute can be specified by using the Type and Configuration columns, with valid types being one of SingleValued or MultiValued while valid configurations are Plain, RectangularArea, CuboidVolume, Range, Quantified, Calculated, or Unconstrained.
Configuration lock
Be aware that once the attribute configuration is set, it will be locked and cannot be changed using subsequent import operations or through the UI.
If the wrong configuration is accidentally committed, the attribute must either be deleted through the UI, or the system name of the attribute must be manually altered through the UI if exiting data needs to be preserved.
The above example will create three attributes, 'WEIGHT' which is single-valued Range attribute with two PDecimal2 fields, 'NAME' which is single-valued Plain attribute with a single PTranslatedStringD field, and 'TAGS' which is a multi-valued Plain attribute with a single PTranslatedStringD field.
Attribute and field names
The name and description of the attribute is specified using the Name and Description columns and can be segmented on translation culture. It is possible to indicate the values in different languages using the same format used for segmented values for an attribute column, but restricted to the relevant format for translation cultures only.
The name of individual fields can be specified using the Field[1-4]/Name column(s), and can be segmented on translation culture as indicated above.
Advanced configuration
Specific field configurations outside the defaults mentioned above can be specified by using either the Field[1-4]/PimType or Field[1-4]/GlobalListSystemName column(s).
Each field can only be configured with either PIM type or global list reference, setting a value for both for the same attribute field will result in a validation error.
In order to allow for idempotent import behaviour (allowing the same import file to be fed into the system multiple times without giving different results) the import will treat setting the PIM type or global list reference of a field to the same value it already has is treated as 'no change' and will not trigger a validation error. If the field configuration is not known at the time of import, a value of NULL can be specified which will also be treated as 'no change'.
Any attempt to assign a different PIM type to the field, or assigning a global list reference to a PIM typed field (or vice versa) will result in an import error (see 'configuration lock' warning above).
Confidential attributes
A confidential attribute is an attribute which should never be exposed the the public via the resolved views.
Confidential attributes can be indicated by including the IsConfidential column, with the value true for those attributes that contain confidential information, and false for those that do not.
A value of NULL will be interpreted as 'no change' in case the attribute exists already, and the default fallback for IsConfidential is false.
Attribute assignment columns
Attribute assignment columns are supported for several of the other import entities, and all follow a common format. This common format is described and defined below.
Generally, if a column for an attribute is present in a CSV file, any entities indicated by rows in this file will be assigned the attribute if it is not already assigned.
If nothing else is indicated, all examples are given in the context of a product import file.
Import mode support
Attribute assignment columns may support both Overwrite mode and Merge mode import. It is defined by the entity under import which import modes are supported for attribute assignment columns.
As a rule of thumb, if attribute assignment columns are supported, Merge mode is always supported, while Overwrite mode may be supported.
Merge mode
In Merge mode, the NULL keyword is regarded as ignore this column for this row. This means that:
- If the attribute does not exist on the item, it will not be added
- If the attribute exists on the product with some value, the value will not be cleared or altered.
In Merge mode, column header heuristics are used to determine what values are being set. This simplifies the attribute column header format significantly for simpler situations, but it means that in theory multiple headers may have the same logical meaning.
Warning
The importer will not identify when multiple headers result in writing to same attribute value. The order of when attributes values are written to the item under import is undefined and not necessarily consistent, resulting in the possibility of a valid CSV file giving different results when imported multiple times.
In order to avoid this behaviour, it is advised to not have different columns that writes to the same attribute value.
As an example of the case where multiple columns affect the same attribute value, consider the following:
Assume that the attribute with system name PLAIN_ATTR is a plain attribute with field name VALUE, and the field takes a PTranslatedStringD. The following column headers will all write to the same attribute value (the attribute field default string value): PLAIN_ATTR, PLAIN_ATTR/VALUE, PLAIN_ATTR:Default, PLAIN_ATTR/VALUE:Default.
Under these circumstances, we can Import the following CSV file:
After the file is imported, the default value of PLAIN_ATTR for Product 1 is set to either of the "Default value case X", and it is not defined what value is set.
Note
In Merge mode, there is currently no way to de-assign an attribute from an entity through a CSV import. It is only possible to add or alter existing attribute assignment.
Similar limitations are imposed on multi value attributes, as described below.
Overwrite mode
In Overwrite mode, the NULL keyword is regarded as setting an empty value. It behaves slightly differently depending on the exact column format.
Overwrite mode does not use heuristics to determine what values are being set. A value can only be changed by specifying one specific column header. This allows Overwrite mode to handle the NULL keyword slightly differently depending on the exact header format.
Note
Overwrite mode require the user to specify the full attribute value when setting attribute values. All field values and all segmented values are touched when just one field value is specified. Omitting parts of the full value set may lead to loss of data.
The only exception is that the importer treats PIM and externally owned values as if they were separate attributes on the product. Specifying headers for externally owned values only will result that the importer does not touch the PIM owned values (thus not touch user inputted values).
Assume that an attribute with SYSTEM_NAME exists with fields FIELD_1, FIELD_2, FIELD_3 and FIELD_4. Let M specify a multi-valued attribute value with index M (whenever attribute is MVA), and let FIELD_N be the system name of attribute field N.
Generally, the following is true:
| CSV header | Supported attribute type | Row value is NULL | Note |
|---|---|---|---|
| SYSTEM_NAME | Single valued attribute Multi valued attribute |
PIM owned values for that attribute is removed from the object. If the object does not have externally owned values for that attribute, the attribute is removed. |
|
| Ext_SYSTEM_NAME | Single valued attribute Multi valued attribute |
Externally owned values for that attribute is removed from the object. If the object does not have PIM owned values for that attribute, the attribute is removed. |
|
| SYSTEM_NAME[0] | Multi valued attribute | PIM owned values is a list of zero value elements | Not yet supported |
| Ext_SYSTEM_NAME[0] | Multi valued attribute | Externally owned values is a list of zero value elements | Not yet supported |
| SYSTEM_NAME/FIELD_N | Single valued attribute | Sets the FIELD_N value of SYSTEM_NAME attribute to NULL (the empty value) for the object under import. Only affects PIM owned values. |
Since attribute SYSTEM_NAME is touched by the importer, other field values are implicitly also emptied (unless specified in separately in other columns) |
| Ext_SYSTEM_NAME/FIELD_N | Single valued attribute | Sets the FIELD_N value of SYSTEM_NAME attribute to NULL (the empty value) for the object under import. Only affects externally owned values. |
Since attribute SYSTEM_NAME is touched by the importer, other field values are implicitly also emptied (unless specified in separately in other columns) |
| SYSTEM_NAME[M]/FIELD_N | Multi valued attribute | Sets the FIELD_N value of value with index M of SYSTEM_NAME attribute to NULL (the empty value) for the object under import. Only affects PIM owned values. |
Since attribute SYSTEM_NAME is touched by the importer, other field values (and field value indexes) are implicitly also emptied (unless specified in separately in other columns) |
| Ext_SYSTEM_NAME[M]/FIELD_N | Multi valued attribute | Sets the FIELD_N value of value with index M of SYSTEM_NAME attribute to NULL (the empty value) for the object under import. Only affects externally owned values. |
Since attribute SYSTEM_NAME is touched by the importer, other field values (and field value indexes) are implicitly also emptied (unless specified in separately in other columns) |
Note
Overwrite mode supports prepending $ to indicate that global list item values are referenced by system name instead of value. See details below.
Attribute field value assignment formats in Overwrite mode is similar to Merge mode (see details below).
In order to assign attribute values, column header has to be expanded to its full size. For example:
Assume that the attribute with system name PLAIN_ATTR is a plain attribute with field name VALUE, and the field takes a PTranslatedStringD, the following column headers will all fail if the row column is not NULL: PLAIN_ATTR, PLAIN_ATTR/VALUE, PLAIN_ATTR:Default. The only legal way in Overwrite mode to assign some value to the field is to use the full header: PLAIN_ATTR/VALUE:Default.
The following CSV is the only supported way of assigning "Default value" to the default attribute value for the attribute 'PLAIN_ATTR' of Product 1:
Is the only legal way to assign "Default value" to Product 1.
Warning
It is recommended to always provide all items of the multivalue collection instead of providing partial updates where there is a risk that items may have been removed from the multivalue attribute before the file is imported.
Note
CSV examples below are written for CSV Merge mode.
Please be careful with specifying the full header when using the examples in an Overwrite mode scenario.
Advanced overwrite scenarios
In order to allow more fine-grained control over attribute operations, in particular as pertains to removal operations, it is possible to switch the importer into Advanced mode on a per-row basis.
To control this import mode, the AttributeAssignmentHandling column can be used.
Note: This mode is only available in overwrite mode. Using the column in Merge mode will result in the import failing validation.
The following values are valid for the AttributeAssignmentHandling column:
| AttributeAssignmentHandling | Meaning |
|---|---|
Standard |
The row is interpreted exactly as if the AttributeAssignmentHandling column was not present.All rules for Overwrite mode are applied as described above. |
| [the empty string] | If no value is given for the column, this is interpreted as if the value Standard was present (see above) |
TreatNullAsDelete |
NULL values are interpreted as deletion in a few cases as outlined below.Outside of these cases, the same behaviour for NULL values as in Standard mode applies. |
Advanced |
Two new keywords are now recognized as such in attribute assignment columns: IGNORE and DELETE.The functionality of these keywords change as follows: IGNORE: No change should be made to this attribute/field/segmentation. This is largely similar to the use of NULL in Merge mode.DELETÈ: The cell is interpreted as a deletion in the cases outlined below. Outside of these cases, the use of the DELETE keyword is invalid.NULL: The standard NULL rules for Overwrite mode apply, same as Standard mode. |
Special deletion cases
As mentioned above, in TreatNullAsDelete and Advanced mode, it is possible to indicate a request to perform data deletions which are not possible in the standard Overwrite mode.
These cases are as follows:
- For a column indicating an attribute system name without any additional header segments, a delete command will remove the attribute entirely from the entitys attribute collection.
- For a column indicating a multivalue attribute item without any additional header segments, a delete command will remove the multivalue item from the item collection.
- For a column indicating a segmented value segment, a delete command will remove the segmentation from the value.
Removing attributes
Case 1 above is illustrated with the following example product import CSV file.
After importing this file, the following changes will occur:
- For the product TSHIRT_SMALL, no changes will happen, as the import is still performed in standard mode.
- For the product TSHIRT_MEDIUM, the
NULLvalue is interpreted as a deletion command, and thus the atribute 'SIZE' is removed from the product. - For the product TSHIRT_LARGE, the
DELETEkeyword is available since we are operating inAdvancedmode, and thus the 'SIZE' attribute is also removed from the product.
Note
When operating in reference mode for global list referencing attributes, $SYSTEM_NAME columns containing a deletion command will also result in the deletion of the attribute from the attribute collection.
It is not possible to issue a delete order for a field of an attribute, since the number of fields is controlled by the attribute definition. The closest thing to removing a field value is issuing a value of NULL for the field, and letting the existing value be cleared.
Removing multi value attribute items
Case 2 above is illustrated with the following example product import CSV file.
After importing this file, the following changes will occur:
- For the product TSHIRT_SMALL, no changes will happen, as the import is still performed in standard mode.
- For the product TSHIRT_MEDIUM, the
NULLvalue is interpreted as a deletion command, and thus the second item of the 'SIZE' multivalue attribute is removed from the product. - For the product TSHIRT_LARGE, the
DELETEkeyword is available since we are operating inAdvancedmode, and thus the second item of the 'SIZE' multivalue attribute is also removed from the product.
Note
To protect against unexpected data loss, removal of multivalue items is limited to only allow deletions from the end of the multivalue list.
This means that in order to remove an item in the middle of a multi value attribute, the remaining items must be manually shuffled such that the item(s) to be deleted are the last of the collection, and then deleted from the end.
Removing segmentations
Case 3 above is illustrated with the following example product import CSV file.
After importing this file, the following changes will occur:
- For the product TSHIRT_SMALL, no changes will happen, as the import is still performed in standard mode.
- For the product TSHIRT_MEDIUM, the
NULLvalue is interpreted as a deletion command, and thus the 'danish' segmentation of the 'SIZE' attribute is removed from the product. - For the product TSHIRT_LARGE, the
DELETEkeyword is available since we are operating in Advanced mode, and thus the 'danish' segmentation of the 'SIZE' attribute is also removed from the product.
If the target attribute facilitates default values, note that it is not a valid operation to delete the default value, since is must always be present. The closest to removing the default value would be to overwrite it with an empty or zero value.
Limitations
Since the expected result is ambiguous, assigning a value to an attribute field while simultaneously deleting it will result in a validation error to protect against unintended data loss.
Simple attributes
Simple attributes with only one field can be indicated using just the system name of the attribute:
Any referred attributes must already have been created and configured in the system to be used; if they are not, it will give a validation error.
Segmented values
Translation culture, channel, device, and market are indicated by adding the suffix of either :TCULTURECODE, :CCHANNELSYSTEMNAME, :DDEVICESYSTEMNAME and/or MMARKETSYSTEMNAME to the column header.
Only the relevant segmentation dimensions must be specified, i.e. when segmenting on TranslationCulture:
Or when segmenting on both Translation og Market:
In merge mode, only the specified segmentations will be touched. Segments for which the value is given as NULL will not be changed.
In overwrite mode, all segmentations will be touched. Segments for which no column is given, will be removed. Segments for which the value is given as NULL will be removed.
In both modes, for a segmented value types, segments which do not already exist are added.
In Merge mode, it is not possible to completely remove a segment from an attribute through he import API after it has been attached.
In Overwrite mode, the importer needs to specify all segmented values for an attribute.
Attributes with multiple fields
The field name can be included after the attribute name and possibly the multi value index, but before the segmentation key.
The field name must match the system name the field was given when the attribute was created. For an overview of the default system names of fields, see standard field names.
In the following example, the attribute 'WEIGHT' was created using a 'Quantified' configuration with the default system names:
Here, values for the fields 'ITEM' and 'QUANTITY' are modified.
If the attribute was configured for multiple values, it would give rise to a header similar to:
Multi valued attributes
The value index is indicated using hard-braces following the attribute name, and before the segmentation key if one is used.
When importing multi valued attribute values, the indices must be consecutive and start from 0.
If the indices have 'holes', the import will fail validation.
The above example only works in Merge mode.
In the example, a single item will be created for the product 'Dumbbell 1kg'. If the NULL value was not used for the two additional indices, three items would have been created, two of which contained an empty string.
In Merge mode, it is not possible to remove values from a multi valued attribute through a CSV import, only to add or update existing values. A NULL value will be interpreted as 'no change'.
In Overwrite mode, the full list of desired values has to be provided. If only one value is specified but the item had four, only the one single value specified in the CSV is set and all other values are removed. A NULL value will be interpreted as empty field value.
Segmented multi valued attributes
The segmentation key should be added after the multi valued index, as specified in the Segmentation segment above:
Global list reference values
Global list referencing attributes can have their values added or updated through import in one of two ways, by item value and by system name reference.
Import by item value
When working by value, the value of global list attributes and predefined value attributes can be included by directly giving the value of the global list item or predefined value in the import set.
The value will be matched against existing values in the global list or predefined values collection, and if it does not already exist as a member, it is added to the collection.
In the following example, the attribute value for the attribute 'WEIGHT' will be updated as a reference to the global list item with the value '42'.
If there are no global list items with this value present already, it will be added to the list first, then referenced.
Import by system name reference
When working by reference, the column header should be prepended with the $ symbol to indicate that the column values should be interpreted as system name references rather than literal values as shown in the following example:
In this example, the attribute value for the attribute 'WEIGHT' will be updated to reference the global list item with the system name 'WEIGHTS_5' in the associated global list.
Note
When importing by system name reference, no attempt will be made to create an item, if no items are already present matching the given system name.
Instead, the failure to match will be treated as an import error, and the import operation will fail for the batch.
In the above example, if no global list item was present with the system name 'WEIGHTS_5', this would cause the import to fail, with an error message indicating the missing item.
Whether all referenced items are present or not cannot be determined in the up-front validation pass and will only occur once the import operation is running on the job server.
Known limitations
In the current version, there are limitations on the types of global list and predefined value references which are possible to utilize in the import/export engine.
In general the referred values should be simple, that is, only non-segmented types can be used for a global list, and only predefined values for an attribute with a single non-segmented field can be handled in the current engine.
The way value addition is handled means that the global list should have 'Unique Values' enabled, to ensure against duplicate values, and guarantee that the correct item is referenced on a match.
If 'Unique Values' is not enabled, no guarantee is given on how the item is resolved.
Externally vs. PIM-owned values
If the value for an attribute is externally owned, this can be indicated by prefixing the column name with Ext_ as follows:
In this case, the WEIGHT attribute will be assigned the value 5500 to its PIM-owned value, and the value 5000 as its externally owned value.
If nothing else is specified, it is assumed that all values are PIM-owned.
Note
When import mode is Overwrite, it is important to remember that externally and PIM-owned values are treated as to separate attributes, which means that change to externally owned values will never influence PIM-owned values.
This is useful, since Overwrite modes expects the full attribute truth when providing just one property of the attribute (ie. setting one segmented value of on field of one index of a MVA).
Thus an importer does not need to worry about PIM-owned values when setting externally owned values.
Standard field names
The default field names for the built-in attribute configurations are as follows.
| Configuration | Field 1 name | Field 2 name | Field 3 name | Field 4 name |
|---|---|---|---|---|
| Plain | Value | N/A | N/A | N/A |
| RectangularArea | Width | Height | N/A | N/A |
| CuboidVolume | Width | Height | Length | N/A |
| Range | From | To | N/A | N/A |
| Quantified | Item | Quantity | N/A | N/A |
| Calculated | Formula | N/A | N/A | N/A |
| Unconstrained | Field1 | Field2 | Field3 | Field4 |
For attributes with only one field, it is optional whether to include the field name.
Format requirements
- The import file must be in CSV format
- The import file must be in UTF-8 format
- The import file must use semi-colon (
;) as the column separator - The length of the import file should not exceed 1.000 lines (unless when the import is batched)
- The import file should be kept to minimum necessary number of columns
For specific representation details, refer to the Roundtrip format documented in Data Types.
In general, numeric and DateTime types are compatible with .Net's InvariantCulture formatter.
Please note the following differences between the Data Types overview and the csv import data representation for referential types:
MediaBank6 types
In addition to allowing the mediabank file ID in {guid} format, an optional cropping ID can be specified using the format "{MediaBankFileId}|CroppingId" where CroppingId is the Guid of the cropping.
The ID referred to is the MediaBank id, and not the PIM id of the MediaBank item.
ContentManagement7 type
CMS7 articles must be referred in the format "{CMS7ArticleId}|SegmentationKey" with the CMS7ArticleID being in {guid} format, and SegmentationKey being the name of a segmentation.
Note
If a set of entities for import have very disparate assignment sets, it is often better to split the import operation into two operations where the entities are organized so as to ensure a maximum overlap of their attribute assignment sets to avoid excessive NULL column values.
Having many empty columns may slow the overall performance of the asynchronous import job, and should be kept to a minimum where possible.