Mass Editing Stock Items with Excel Export/Import

You can make mass edits to stock item information by exporting your stock items in Excel format from the Back Office, and then editing the information with Excel. After you have saved the modifications you can then import the stock item information back to the Back Office.

It is also possible to create new stock items by filling out a stock item template with new stock item information, and then importing the template to the Back Office. You can download the stock item Excel template by clicking the Download Templates icon in the toolbar of the Stock Items view.

Open the file you downloaded (GlobalKeepingUnitTemplate.xlx) and enter information for the new the stock items.

SKUs

All the stock items in Commerce Cloud have an SKU, which is a unique identifier. SKU is mandatory information when you import stock items to the Back Office; when a new stock item is imported to the system for the first time, you can define its SKU freely.

The SKU of the stock item is also visible in the the URL of the product, as seen in the example below.

The following table details the fields in the stock item export/import Excel file.

Column	Type	Mandatory/Optional	Description
sku		M	SKU of the stock item. Must be at least 3 characters long.
supplierGln		O	Supplier’s Global Ledger Number.
supplierId		O	Supplier’s product ID.
manufacturerProductId		O	Manufacturer’s product ID.
name		M	Name of the stock item.
baseUnit		M	Base unit of the stock item.
category:group:id		M	Identifier of the stock item’s category in the category type group.
category:group:Name		M	Name of the stock item’s category in the category type group.
category:[category name]:id		O	Identifier of the stock item’s category in a category type.
category:[category name]:Name		O	Name of the stock item’s category in a category type.
taxpercent		M	VAT percent of the stock item. This field is technically optional, but if it is left empty, the VAT percent will automatically be set to 0%, so this field should always be filled in.
stockUnit		O	Stock Unit used when counting the stock. If this field is left empty, the system will automatically set the value to be the same as baseUnit when the products are imported.
stockUnitName		O	Name of the stock item’s package. For instance: 6-pack.
stockUnitSize		O	Package conversion rate (the amount of Base Units in the package.)
stockPackageGtin		O	Global Trade Item Number of the package.
stockUnitPurchasePrice		O	Purchase price of a single stock unit. Used in conjunction with stockUnitName and stockUnitSize to set purchase price for a package.
baseUnitPurchasePrice		O	Purchase price of a single base unit. If left empty, the purchase price will automatically be set to €0.
capacityUnitCode		O	Net content unit of the stock item. Can be used if baseUnit is set to pcs, btl or pkg. For instance: cl for centiliter net content or kg for kilogram net content.
capacityUnitAmount		O	Net content amount of the stock item. Used in conjunction with capacityUnitCode to set unit amount for net content. For instance: If capacityUnitCode is set to cl, capacityUnitAmount can be set to 70, to have a bottle with net content of 70 cl.
isAppliedtoAllStocks		M	Defines whether the possible changes made to the purchase price of the stock item are applied to all stocks. Possible values: FALSE (not applied to all stocks) or TRUE (applied to all stocks).

Export and Edit Stock Item Information

1. (optional) In the Stock Items view, narrow down the list of stock items by entering a search term in the Search… field, or selecting the Selling item not created filter from the Filters drop-down and then clicking Apply Filters. The stock item list is updated accordingly.

   

2. When the stock item list displays the stock items whose information you want to edit, click the Export to excel icon in the Back Office toolbar.

   

3. The ExportedStockItems.xlsx file is downloaded to your computer.

4. Open the file in a suitable program such as Excel and edit the information.

   • Do not change the the sku field of the stock item, because that will result in system not being able to update the stock item when it is imported.

   • Stock item lists can be used for other purposes than importing them back to the Back Office; importing any files that have been exported from the Back Office is not mandatory.

5. Save your changes to the Excel file and click the Import from file icon from Back Office toolbar.

   

6. Select the file you saved and click Open.

7. If the import was successful, the notification File has been successfully uploaded is displayed in the Back Office.

8. You will receive a message in your e-mail when the processing is done. If importing the file was unsuccessful, the e-mail also contains the reason(s) for this.