Prepare Spreadsheet for Import

Guidelines to create a spreadsheet for import

The five compatibility checks for a successful import are as follows:

  1. SKU - *Mandatory Column*
  2. One product = one row
  3. Seven sub-levels for categories
  4. Declare parents for product variations
  5. Link to files must be publicly accessible http-links

1. SKU - Mandatory Column

Below is an example of a compatible spreadsheet. Only one column is mandatory; SKU. All other columns will be imported as product attributes that you can define during the import mapping process.

Screenshot 2019-03-05 at 12.04.42

2. One product, one row

All rows in the sheet must be individual products (1), except the first row which must be a header row for the columns (2), that contains names for each attribute.

2.1

As (1) above shows, all information about a product must be contained within a single row.

Attributes with multiple values such as colors, materials etc. must be in one cell in that row, and each value needs to be separated by a comma within the cell. In example:

Screenshot 2019-03-05 at 12.36.20

*When importing attributes with multiple values, you can use a different separator than comma by configuring the import (1).

Screenshot 2019-03-08 at 11.20.56

3. Six sub-levels for categories

You can assign as many categories as you want to a product.

However, in order to enforce a sound organization of products, as well as comply with industry standards, category structures are limited to six levels of subcategories below a root category.

For example, a pair of pants could have the following categories:

Clothing > Pants > Denim
Collection > 2017 > AW17
Men > Bottoms > Casual Pants > Denim

Assigning these categories to a product in a spreadsheet is done by:

  1. Defining the category hierarchy using the > symbol
  2. Separating multiple values using a comma

Example:

Clothing>Pants>Denim,Collection>2017>AW17,Men>Bottoms>Casual Pants>Denim,Men’s Clearance>Denim

Notice that the ‘>‘ sign is used to define the hierarchy of the categories from top to bottom. One product, one row is followed here by separating multiple values for a single column with a comma.

4. Declare parents for variations

Importing product variations requires that you designate a parent product. This product will become the “root” variation under which all the others are organized.

For example:

The Raw Jeans in the example below has designated the small size with the SKU = AW1701 as the parent. The medium and large sizes are variations of this product.

Variations parent and child

To import variations correctly, make sure that:

  • You have a parent product that all variations (or child products) refer to in the column 'Variation Of'
  • No child product refers to another child product
  • Parent products are the “root” and therefore have the 'Variation Of' column empty
  • The child products follow the One product, one row rule and have all information in its row, even if it means having duplicate data across all child product rows as well as the parent.

5. Link to files must be publicly accessible http-links

It is possible to import the digital assets you have for your products by providing publicly accessible http-links to your assets. If you have multiple assets for a product, simply split the links with a comma, and each file will get downloaded separately and assigned to the product.

Screenshot 2019-02-14 at 13.04.18

Additional information

Functional Columns (PIM System Attributes)

Functional columns are special columns in the spreadsheet that allow you to do pre-import organizing. These columns define relationships instead or other organizational needs. Non-functional columns simply provide data input.

Most of the functional columns have already been described in the above section, here is a formatted overview over the functional columns for PIM imports.

*It is not compulsory to provide information for all of these attributes, nor do you need to create these attributes as they are already provided in the system for you.

System Attributes Import_new

Attributes with Asterisks*

While matching and mapping attributes, you will notice that some attributes have either one or two asterisks

  • One asterisk* indicates that these attributes accept multiple values and those multiple values need to be separated by a comma, For example: black, white, purple, blue, red, yellow, green

  • Two asterisks ** is a category hierarchy. Category hierarchies are set up using > to indicate subcategories, and commas to indicate new root categories: category1>subcategory1, category2>subcategory1>subcategory2

Work Smarter

If you need to edit all rows in a column, doing it manually should be your last resort. Always google your issue first. The challenges you may face when preparing your spreadsheet have likely already been solved by others.

At Plytix we are primarily using Google Sheets to prepare sheets for import. What we find particularly nice about using it, is the Marketplace for Add-ons which makes reformatting super easy. Take a look at the selection of add-ons we use on day-to-day basis.