Import catalog items and inventory

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

This section applies to Open Storefront Framework (OSF) and Storefront Classic.

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 Commerce:

Create a spreadsheet in the correct format for importing to 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 (_).

For 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 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 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).

The following illustration shows an example of the prepend format used to add products to collections:

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 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, Commerce cannot validate the CSV file and the import will fail.
You can create a new legacy catalog by specifying its catalogVersion value. The catalogVersion column includes an integer that specifies the type of catalog. 1 specifies a legacy catalog and 2 (default value) specifies an independent catalog. You can create legacy catalogs only if you configured Commerce to support them. See Create legacy catalogs for information about how to use the Admin API to enable support for legacy catalogs.
Note: You cannot change an existing catalog’s catalogVersion during import. If you do, Commerce cannot validate the CSV file and the import will fail.

The rootCategories column includes a comma-separated list of Collection IDs for the root collections in each legacy catalog. This cell must be blank if the catalogVersion value is 2; otherwise, the import will fail.
If you are creating or updating a legacy catalog, the only values you can add to the rootCategories cell are the IDs of root collections for the Product Catalog, including rootCategory and nonNavigableCategory. See Understand catalogs to learn about the Product Catalog.

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, 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, Commerce displays the outcome of the import in a results dialog box.

File validation

When you select a file for import, 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: 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, 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, 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 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 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 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.