Import and Export Catalog Items and Inventory

10 Import and Export Catalog Items and Inventory

You can export products, SKUs, collections, and catalogs to a file in Comma Separated Value (CSV) format so you can work with them outside of Retail Digital Commerce and easily import changes back into your Retail Digital Commerce environment.

The following are examples of changes you can make by exporting and importing data:

If you are a new Retail Digital Commerce customer, you can import an existing catalog to initially populate your storefront.
If new catalog information arrives on an automated feed, you can format and edit the new assets in bulk and import them into your catalog.
If you receive updates from a number of people, you can incorporate all the changes into your catalog at once instead of manually entering each change.
You can quickly make bulk edits or perform global operations (such as search and replace or spell-checking) on catalog assets in a spreadsheet and then import those changes back into your catalog.
You can distribute asset information for review to people who do not have access to the Retail Digital Commerce tools.
If you let shoppers view your store in different languages, you can import translations for each language your store supports.

You can also import inventory data, such as inventory count and stock threshold, for products and SKUs into your catalog.

Understand Export Guidelines

It is important to plan before exporting your data.

This section provides information that helps you plan and understand your export.

You can export products, SKUs, collections, and catalogs.
You can export only one type of item in each export procedure. For example, you cannot export both products and collections to the same spreadsheet; you must export the products in one export procedure and then export the collections in another.
Exporting catalogs exports properties that describe each of your catalogs, such as ID, display name, and root categories. Exporting catalogs does not export the products, SKUs, and collections the catalogs include.
When you export products, SKUs, collections, and catalogs, all available properties are automatically exported. You cannot change the list of properties to export.
If you modified a product, SKU, collection, or catalog, the modified version is exported, even if you have not published the changes. For items you have not modified, the published version is exported.
If your Retail Digital Commerce instance uses multiple catalogs, you can export products, SKUs, and collections from a specific catalog or from all catalogs. You can also export items in Unassigned Products and Unassigned Collections, which are not associated with any catalogs.

Export Catalog Items

This section includes the following topics:

Export Products, SKUs, and Collections

Follow these steps to export products, SKUs, or collections to a spreadsheet:

On the Catalog page, click the Export button.
Select the items to export: Products, Product Variants (SKUs), or Collections.
If your Retail Digital Commerce instance includes multiple catalogs, specify which catalogs to export from: Select All Catalogs to export the selected items from all catalogs. This is the default selection.
If your store supports more than one language, select the language to export from the Language list. This list does not appear if your store supports only one language.
If you want to export a subset of items, select Filter Items Being Exported See Specify export filter criteria for more information.
Click Export.
Once you click the Export button, the operation cannot be canceled and you cannot work on other tasks until the export is complete.
When the export is complete, your browser displays a dialog box where you choose whether to open or save the spreadsheet.
If you click Cancel in this dialog box, the export file is not saved and you must repeat the export procedure.
See View the Exported data for information about how to understand the exported data.

Export Catalogs

Follow these steps to export information about all catalogs to a spreadsheet:

On the Catalog page, click the Export button.
Select Catalogs from the list of items to export.
If you want to export a subset of items, select Filter Items Being Exported See Specify export filter criteria for more information.
Click Export.
Once you click the Export button, the operation cannot be canceled and you cannot work on other tasks until the export is complete.
When the export is complete, your browser displays a dialog box where you choose whether to open or save the spreadsheet.
If you click Cancel in this dialog box, the export file is not saved and you must repeat the export procedure.
Read View the Exported data for information about how to understand the exported data.

Specify Export Filter Criteria

By default, when you export products, SKUs, collections, and catalogs, Retail Digital Commerce exports all of the type of item you selected. To export fewer items, you can create rules that filter the items based on properties you specify, such as a parent collection for products.

Note:

You cannot save filter rules. If you select a different type of item to export or navigate away from the Catalog page, any filter rules you created will not persist when you return.

To create a rule while exporting items, follow these steps:

In the Export Catalog dialog, select Filter Items Being Exported.
The operators you see depend on the type of property you selected.
If you selected a specific catalog to export from and that catalog has filtered views, you can select one from the Select Filtered View list. The default selection is All Filtered Views.
Click Add Rule.
Select a property from the drop-down list in the rule. The available properties depend on the type of items you are exporting. You can select only one property per rule.
Select an operator that links the property to a value that you will specify in the next step.
The operators you see depend on the type of property you selected.
Select or enter a value for the property.

For some values, such as Parent Collections, Retail Digital Commerce displays a list for you to choose from. For others, such as Name or ID, you must type in a value. The rule editor does not validate any text, numeric, or date values you enter.
If you create more than one rule, select one of the following options from the Match list to specify how the rules behave together. You can select only one option for the entire set of rules.
- Any: If an item matches any rule in the set, it is exported.
- All: (default) If an item matches all the rules in the set, it is exported
Click Export.

View the Exported Data

The exported CSV file can be viewed and edited using a spreadsheet program such as Microsoft Excel. The default file name follows the format <YYMMDDhhmmss>export.csv. For example, a file exported January 30, 2017 at 11:43:27 AM is named 170130114327export.csv.

The exported data follows a specific format that can be used to re-import the spreadsheet to Retail Digital Commerce.

Row One: Repository, Item Type, Formatting Options, and Process Messages

The first row is reserved for the repository path and item type, any formatting options, and any warning or error messages.

The first column of row one (cell A1) contains the repository path and the item type separated by a colon. For example: /atg/commerce/catalog/ProductCatalog:product
Columns two through six (cells B1, C1, D1, E1, and F1) contain data formatting options:

TIMEFORMAT appears in the third column (cell C1) and shows the format used for dates with a time stamp, for example, TIMEFORMAT=MM/dd/yyyy HH:mm:ss.

If your store supports more than one language and you selected a different export language than the default language for your store, LOCALE appears in the fifth column (cell E1) and shows the ISO locale format for the language, for example, en_US.

If your Retail Digital Commerce instance includes multiple catalogs and you selected a specific catalog to export from,CATALOG appears in the sixth column (cell F1) and shows the catalog ID, for example, CATALOG=movieCatalog.

Row Two: Property Headings

The second row displays column headings that contain the property names of the exported properties. The ID property is always the first column (cell A2) and is always labeled “ID” regardless of the actual repository name of the ID property.

Note: The spreadsheet contains the Property ID and Label of the property and not the localized display name.

Rows Three and Greater: Data

Catalog data begins in the third row and continues for the remainder of the spreadsheet. If an item does not have a value for a property, the corresponding cell is blank.

Data for filtered catalogs

When you export products or SKUs and your Retail Digital Commerce instance contains filtered catalogs, the second row of the exported spreadsheet includes column headings that contain the following property IDs:

The coreProduct column specifies whether the item is a core product in the catalog.
The filteredCatalogs column includes the Catalog IDs that specify filtered catalog the item belongs to.

See Work with filtered catalogs for more information.

Data for exported catalogs

When you export catalogs, the second row of the exported spreadsheet always displays column headings that contain the following internal property IDs.

The ID column includes the Catalog ID for each catalog.
The displayName column includes the Catalog Name for each catalog.
The catalogVersion column includes an integer that specifies the type of catalog. 2 specifies an independent catalog and 3 specifies a filtered catalog. See Understand catalogs for more information.
Note: You cannot change a catalog’s catalogVersion value if you import the file back into Retail Digital Commerce. If you do, the import will fail.

The rootNavigableCategory column includes the navigable root collection for each catalog whose catalogVersion value is 2. The default value for this property is rootCategory.
The rootNonNavigableCategory column includes the non-navigable root collection for each for each catalog whose catalogVersion value is 2. The default value for this property is nonNavigableCategory.
The defaultCategoryForProducts column includes the Collection ID of the collection that will automatically be the parent of any product created or imported in this catalog without a specified parent.
This column contains values only for catalogs whose catalogVersion value is 2.

Import Catalog Items and Inventory

The import feature lets you work with products, SKUs, collections, and catalogs outside of Retail Digital Commerce and import them, in spreadsheet format, back into Retail Digital Commerce.

Importing provides a convenient method for adding items and batch editing.

You can also import inventory data for products and SKUs into your catalog. Importing inventory is a separate procedure from importing products, SKUs, collections, and catalogs.

The following procedure is an overview of the steps you perform to import either catalog items or inventory from a spreadsheet into Retail Digital Commerce:

Create a spreadsheet in the correct format for importing to Retail Digital Commerce. For more information see Export Catalog Items and Format a File for Import.
Load the file and validate that the items can be imported.
Import the spreadsheet. For more information, see Import Products, SKUs, Collections, or Catalogs.
Review the outcome of the import for any errors or warnings. For more information, see Validate and Preview Items.
Review the imported items on the Catalog page.

Format a File for Import

The easiest way to format a file for importing products, SKUs, collections, or catalogs is to start by exporting a file from your catalog that contains the items you want to modify. If you are importing inventory, you must manually create the import file starting with a blank spreadsheet.

Caution: If you leave a cell blank, a property value might be deleted. See instructions later in this section for marking data to ignore.

Required formats for importing a spreadsheet

The first row and first column of the spreadsheet are reserved for use by the import process and must contain specific information. The first row contains information about the data that is being imported and the first column (except for row 1) contains the ID property of the items being imported.

In the first row of the spreadsheet, you must specify the repository, item type, and any special formats for data types, as follows:

For catalog items: The first column of row one (cell A1) must contain the internal component path and should be left exactly as exported when you created the import template. For example, the following path indicates that the spreadsheet contains products:
/atg/commerce/catalog/ProductCatalog:product

For inventory: Cell A1 must contain the following path:
atg/commerce/inventory/InventoryRepository:inventory

Use the additional columns in row one (cells B1 through D1) to specify any special property formatting, such specific date formats. Each format must be in its own cell, but it does not have to appear in any particular order. The following table describes the formatting options:

Description	Format in Spreadsheet	Requirements
Format of date property values, such as 01/31/2009	DATEFORMAT=MM/dd/yyyy The day, month, and year indicators can appear in any order to match your data, for example dd/MM/yyyy	Required if any imported properties are of type date. The date value must exactly match the format, for example 03/15/2014 instead of 3/15/14.
Format of timestamp property values, such as 31/01/2009 22:45:01	TIMEFORMAT= MM/dd/yyyy HH:mm:ss The day, month, and year indicators can appear in any order to match your data, for example, dd/MM/yyyy HH:mm:ss To use 12-hour time, the format is MM/dd/yyyy hh:mm:ss aa, where aa can be either AM or PM, for example 03/15/2009 05:30:00 AM	Required if any imported properties are of type timestamp. The timestamp value must exactly match the format, for example 03/15/2014 05:30:00 instead of 3/15/14 5:30.
ISO locale format of the language you are importing. See Localize Your Store for more information about importing translations.	LOCALE=locale The ISO locale format for the language, for example, en_US.	Required if you are importing translations for a particular language.
ProductCatalog:sku	LOCALE=locale ID=SKU displayName= parentProduct= Type= configurable=TRUE/FALSE productLine= productFamily= barcode= active=TRUE/FALSE nonreturnable=TRUE/FALSE bundledSkusItem=item1:Sku_5Cxy\|item2:Sku_27Gxyzii bundledSkusQuantity=item1:2\|item2:1 configurationMetadataName= configurationMetadataValue=	The ISO locale format for the language, for example, en_US. Qualified SKU Text name Parent product, if any. An empty string or a valid type that is defined in the repository. TRUE/FALSE Product Line, if any. Product Family, if any. Qualified barcode TRUE/FALSE TRUE/FALSE SKUs in the bundle. Quantity of each SKU in bundledSkusItem Meta data name, if any. Value for configurationMetadataName

Beginning with the second row, the first column of the spreadsheet is reserved for the ID property of the imported items. No other property can occupy column one.

For catalog items: The second row (cell A2) contains the ID heading, which is always ID regardless of the actual name of the repository item’s ID property. The remaining rows (cells A3 and greater) contain the ID values for each imported item. Any rows with blank ID values are skipped during import.
For inventory: The second row (cell A2) contains the ID heading, which is always catalogRefId regardless of the actual name of the repository item’s ID property. Cell B2 contains the label stockLevel. Cell C2 contains the label stockThreshold. Cell D2 contains the label preorderLevel. Cell E2 contains the label preorderThreshold. Cell F2 contains the label backorderLevel. Cell G2 contains the label backorderThreshold. Cell H2 contains the label availabilityDate.

Keep in mind that ID values can contain only alphanumeric characters, hyphens (-), and underscores (_).

FFor details about preorder and backorder levels and thresholds, see Manage Inventory for Preorders and Backorders.

Import File Size limitations

The maximum number of item rows that can be imported from a single spreadsheet is 25,000. If you import more than 25,000 items, any items above that limit are not imported and the import report includes a message that tells you the import was truncated.

Format for Importing Non-ASCII Characters

When you import text properties (such as display names for products, SKUs, and collections) that contain non-ASCII characters, you must save the CSV file with UTF-8 encoding before you import it. Otherwise, the non-ASCII characters will appear garbled in the administrative interface.

Note:

When you export text properties that contain non-ASCII characters, those characters do not automatically display in the exported CSV file. In order to display these characters in the CSV file you must save it with UTF-8 encoding

Format for importing custom-property values

If your spreadsheet includes values for custom properties, it must include a column with the heading type in the second row. For each row that includes a value for a custom property, this column must include the ID of the product type that contains the property.

Format for adding new items

The import feature allows you to create new items when you import your spreadsheet. To create an item, add a row to the spreadsheet and enter property values for each column. If you leave a cell blank, then the value for that property is set to null. If you leave the ID column blank, the item is skipped and not created.

Creating new items is supported only for importing products, SKUs, and collections. It is not supported for importing inventory.

You assign an ID value for the new item in one of the following ways:

Enter NEW in the ID column to let Retail Digital Commerce create an ID.
The new item is created using an automatically generated ID. The NEW keyword must be typed in all capital letters and must be the only value in the cell.

Enter a unique ID in the ID column.
A new item is created with the ID you enter.

To create a new top-level collection, in addition to adding the new row, you must also add the new collection’s ID to the fixedChildCategories column of the rootCategory row. For example, if you add a row to the spreadsheet to add a new category whose ID is cat5000, you must also add the ID cat5000 to the fixedChildCategories column of the rootCategory. Otherwise, the new collection will not appear in the Catalog tree in the administration interface.

Note:

To create an item, you must provide values for required properties.

Format for adding new items to specific catalogs

You can use import to create new products, SKUs, and collections in specific independent (version 2) catalogs. (By default, products, SKUs, and collections are imported into the main Product Catalog that is included with Retail Digital Commerce.) See Work with independent catalogs for more information.

In order to create new items in specific catalogs, you must enable support for the feature. To do this, you issue a PUT command to the adminConfiguration endpoint in the Admin API to set the value of the useCatalogHeaderForCreatingNewItems property to true. For example:

PUT /ccadmin/v1/merchant/adminConfiguration  HTTP/1.1
Authorization: Bearer <access_token>
Content-Type: application/json

{
   "useCatalogHeaderForCreatingNewItems": true,
   "useCatalogHeaderForCreatingNewItems": true,}

To create new products, SKUs, or collections in a specific catalog, include CATALOG=<id> in the first row of the CSV file, where <id> is the Catalog ID of the catalog where you want to create the items. You must specify the ID of an existing independent catalog.

New collections without a specified parent are created in Storefront Navigation in the specified catalog.

If you configured the catalog to have a default collection for products, new products without a specified parent are created in that collection. Otherwise, new products without a specified parent are created in Unassigned Products. See Import catalogs for more information.

If the catalog is associated with existing filtered catalogs, you can specify valid filtered catalogs by Catalog ID in the filteredCatalogs column.

If products are to be directly linked to catalogs, you can specify valid independent catalogs by Catalog IDs in the directCatalogs column. If you are importing products into a specific catalog (that is, if the first row of the CSV file includes CATALOG=<id>), the value in a product's directCatalogs cell must match the CATALOG=<id> Catalog ID if that product is being linked directly to that catalog

Format for deleting property values

You can delete a property value of an item when you import it. To delete a property value, leave the corresponding cell blank.

If you try to delete a required property value, such as pricelist, the spreadsheet cannot be validated and the import will fail.
If the property has a default value, leaving the cell blank resets the value to the default.
If you delete a cell in the spreadsheet but do not want to delete the property value during import, you can mark the cell as ignored. See Marking Data to Ignore.

This delete format does not apply when you are adding items to collection properties. If you format a column to prepend or append items to a collection, blank cells are automatically ignored during import -- nothing is added or removed from the collection. See Format for adding items to a collection property.

Marking data to ignore

When you import a spreadsheet, the file might contain data that you do not want to include in the import, for example, columns with formulas or notes, columns containing a property that you do not want to edit, or blank cells that might cause data to be deleted. To remove these items from the import, you can mark columns (properties), rows (individual items), or cells (individual property values) to be ignored by the import process.

Use the keyword IGNORE, in all capital letters, to mark data that you want to ignore during import, as shown in the following table:

To ignore this	Do this
Ignore a column of data, such as a single property or extra column	Type `IGNORE` in the column heading (row 2).
Ignore an item (a single row)	Type `IGNORE` in the ID cell for the item (column 1).
Ignore a property for an item (a single cell)	Type `IGNORE` in the cell.

Format for adding items to a collection property

You can add items to a collection property by typing an ampersand (&) before or after the property name in the column heading of a collection property, as follows:

Insert the ampersand at the beginning of the heading name to prepend items to the beginning of a collection, for example &fixedChildProducts.
Insert the ampersand at the end of the heading name to append products to the end of a collection, for example fixedChildProducts&.

If you already use the ampersand character as part of a collection property name, you can use a different character for appending and prepending items to collections.

The prepend and append formats simplify the process of editing collections because you only need to include additional items and not every item in the collection. If you do not use this format, the import process overwrites the collection property with the exact items listed in the spreadsheet.

When you use this format, leave a cell blank to indicate that nothing should be added to the collection. If you have formatted a column to prepend or append items to a collection, blank cells in that column are ignored during import (nothing is added or removed from the collection).

Import Catalogs

In addition to the rest of the information about importing described in this topic, keep the following points in mind when you import catalogs into Retail Digital Commerce.

The first column of row one (cell A1) must contain the following string:
/atg/commerce/catalog/ProductCatalog:catalog

If you created an import template by exporting catalogs, this string will already be in the spreadsheet and should be left exactly as exported.

You can create a new filtered catalog by specifying its catalogVersion and baseCatalog values. The catalogVersion column includes an integer that specifies the type of catalog. 3 specifies a filtered catalog. The baseCatalog column must specify the catalog ID for the independent catalog that the new filtered catalog is based on. If the baseCatalog value does not specify an existing independent catalog, the import will fail.

Note:

You cannot change an existing catalog’s catalogVersion during import. If you do, Retail Digital Commerce cannot validate the CSV file and the import will fail.

The rootNavigableCategory column includes the navigable root collection for each catalog whose catalogVersion value is 2. The default value for this property is rootCategory.
You cannot change the value of rootNavigableCategory for an existing catalog and this cell must be blank if the catalogVersion value is 1; otherwise, the import will fail.

The rootNonNavigableCategory column includes the non-navigable root collection for each for each catalog whose catalogVersion value is 2. The default value for this property is nonNavigableCategory.
You cannot change the value of rootNonNavigableCategory for an existing catalog and this cell must be blank if the catalogVersion value is 1; otherwise, the import will fail.

The defaultCategoryForProducts column includes the Collection ID of the collection that will automatically be the parent of any product created or imported in this catalog without a specified parent.
This column can contain values only for catalogs whose catalogVersion value is 2; otherwise, the import will fail.

Validate and Preview Items

When you import a file, Retail Digital Commerce validates the file, first verifying that the file is formatted correctly and contains data that can be imported, then validating the spreadsheet. If you receive errors or warnings prior to the actual import, you can stop the task, edit the spreadsheet to remove any errors, and then upload the file again.

After executing the validation phases and importing the spreadsheet, Retail Digital Commerce displays the outcome of the import in a results dialog box.

File validation

When you select a file for import, Retail Digital Commerce validates the file format and the formats of the first two rows of the spreadsheet. If there is an error in the file, a message appears on the Import dialog.

Errors that can occur during this phase include the following:

The file is not in CSV format.
The repository or item type in the first column of row 1 is not valid.
The file is missing the ID column or it is not the first column.
Row 2 of the file contains properties that do not exist for this item type or have a misspelled property name.
The file contains properties that are not allowed.

Note:

Retail Digital Commerce does not export these types of properties, so if the spreadsheet contains them, they were added to it manually and should be removed.

The file contains a timestamp or a date property but does not have a corresponding TIMESTAMP or DATE format in row 1.

Item validation

After the file is uploaded and passes file validation, Retail Digital Commerce validates all items and displays the outcome in the Import window. You can also download a full validation report.

The validation process determines how many imported items have changed, how many are new, and how many are unchanged.

Note:

To improve performance, Retail Digital Commerce does not check to see if a SKU has duplicate variants during item validation. If you find duplicate SKU variants in your catalogs, you can simply delete them.

Errors or warnings that can occur during item validation include:

The value in the spreadsheet cannot be converted to the correct type for a property, for example, a date does not include two digits for the month, or a letter or symbol appears in a numeric property.
A value for a required property is missing.
A repository ID in a property value cannot be found.
If the unknown ID is for the same type of item that is being imported, the message is a warning because the item might be created elsewhere in the spreadsheet. If it is a different item type, the message is an error.

Note:

If the ID column contains a non-existent repository ID, a new item is created with that ID. See Format for Adding New Items.

Import Products, SKUs, Collections, or Catalogs

Before you import your products or SKUs for the first time, you must create product types and variant properties in Retail Digital Commerce. For more information, see Manage Your Catalog.

Follow these steps to import products, SKUs, or collections from a spreadsheet:

On the Catalog page, click the Import button.
In the Import dialog, click Browse and locate the CSV file to import.
Click Upload File.
Click Validate.
Note: Depending on the size of the spreadsheet, this process could take several minutes to complete.

The Import dialog displays the total number of changed, new, and unchanged items, as well as any errors or warnings. To see a full validation report, click Download Full Report.
Click Import.

The new and changed items are added to the catalog. You can now publish them to your storefront.

Note:

If direct price editing is enabled for your Retail Digital Commerce store, any price changes you import are available on the storefront without publishing. Other product changes, however, must still be published. As a result, prices may exist on your production system before the associated products do. These prices will be applied to the products when they are published. See Update prices without publishing for more information.

Import Inventory

Follow these steps to import inventory information for your products and SKUs from a spreadsheet into Retail Digital Commerce:

On the Catalog page, click Manage Catalogs and select Inventory.
On the Manage Inventory page, click the Import button.
In the Import dialog, click Browse and locate the CSV file to import.
Click Upload File.
Click Validate.

Note:

Depending on the size of the spreadsheet, this process could take several minutes to complete.

The Import dialog displays the total number of changed, new, and unchanged items, as well as any errors or warnings. To see a full report, click Download Full Report.
Click Import.

The new inventory data is added to your catalog and is automatically updated on your storefront.