Guidelines to create a spreadsheet for import
The five compatibility checks for a successful import are as follows:
- SKU - *Mandatory Column*
- One product = one row
- Seven sub-levels for categories
- Declare parents for product variations
- 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.
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.
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:
*When importing attributes with multiple values, you can use a different separator than comma by configuring the import (1).
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:
- Defining the category hierarchy using the > symbol (though other symbols are accepted, this is the default option available)
- Separating multiple values using a comma
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.
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.
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.
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.
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
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.
- Merge Sheets to combine data from multiple sheets.
- Merge Values to merge data in columns, rows, or ranges.
- Remove Duplicates to remove or mark duplicates from multiple sheets.
- Split Names to break name parts into multiple columns.
- Advanced Find and Replace to search through values, links, and notes, and save the results to a new table.
- Find Fuzzy Matches to find all items that are similar to your query.
Import CSV files have a 20MB Limit